The usual approach is to convert the measure to a text value (string) and manually add the "%" sign or "$" sign depending on a logical test in the calculated field.
Can you go back to your original data format (before you pivoted) and just not include that one measure in the pivot? Then you'll have a "pivot values" column that is only "%" values, and one single column for the "$" value.
Thank you so much. I get the general method of doing this but don't why it doesn't work with my case.
Can you download my original workbook and remove pivot to get the original data format before pivot as you want? I don't know how to attach the new workbook from here. This is my first time posting a question in this forum.
Thank you for your help in advance,
Sure. Here you go.
Is that more what you were looking for?
And by the way, well done for a first question! You asked it clearly, and you provided a packaged workbook. Makes it a lot easier to help!
EXAMPLE.ANH.twbx 75.6 KB
Thanks for your help! I highly appreciate.
However, "wage mean" must be one of the indicators to be selected from the filter. That's why I have to add "wage mean" to pivot table along with other indicators. The problem is that "wage mean" values appear as "%" (same format as other indicators), NOT "$" as expected.
Do you know how to keep the value formats dynamic in this case?
This is a hard problem for a couple of reasons.
We have to convert a Numeric field to a String, and when we do that Tableau causes some issues. Refer to this thread for details.
Once we get past that issue, it's very hard to control the formatting of the numbers properly. For instance, I can't get a comma in the Wage Mean measure without a LOT of extra text string manipulation.
However, this workbook should give you an idea of what to do. I broke it down into 2 separate calculations
1) Use the Pivot Measure Values field and return a properly truncated string of the numeric value ("Values")
2) Add "$" or "%" to the "Values" field depending on the selected Pivot Measure Name (Wage mean)
EXAMPLE.ANH.twbx 75.0 KB
Yes, it is exactly what I want. Thank you so much!!!
But it would be better if I can get the comma in the Wage Mean. To insert ",", I saw someone else in our group do like this but I don't really understand so I can't apply correctly:
IF len(str([Wage mean]))<3 THEN
"$" + str([Wage mean])
ELSEIF len(str([Wage mean]))<=6 THEN
"$" + LEFT(str([Wage mean]), Len(str([Wage mean]))-3)
+ "," + RIGHT(str([Wage mean]), 3)
ELSEIF len(str([Wage mean]))<=9 THEN
"$" + LEFT(str([Wage mean]), Len(str([Wage mean]))-6)
+ "," + MID(RIGHT(str([Wage mean]), 6), 1, 3) + ","
+ RIGHT(str([Wage mean]), 3)
1 of 1 people found this helpful
That's what I mean by "complicated".
What that code does is relatively straightforward because it's only dealing with the single measure "Wage Mean". Here is what it is doing:
If Wage Mean has 3 characters or less, add "$" to the front of the value.
If it is 6 characters or less, find the 3rd character from the RIGHT of the string, and put a "," there.
It it is 9 characters or less, find the 3rd and 6th characters and put "," there.
All of that code is taking a text string, finding a specific spot in that text string, splitting the string, inserting a specific character at the spot where you split the string, then putting the string back together.
That does NOT account for decimal places, however, which makes it trickier to do.
I'm afraid I don't have enough time free right now to dig any deeper, but if you play around with the code you should be able to figure out how it does what it does.
Thanks for your quick answer! You are very helpful. I will spend more time playing with it. Hopefully, I can do it. If not, your answer is already a very good solution for me!
I have checked the numbers shown/displayed on the map. And they were distorted through the calculations. Just want to let you know! I will try to solve it out.
Thank you anyway,