Great question. The closest I can seem to get is by using a calculation like this:
IF SUM([Sales]) >= 1000000 THEN SUM([Sales])/1000000
with the following formatting:
That produces the correct formatting but only for million and thousand values - not anything below 1k. It's strange - we don't show the text in  in the output, so it's obvious Tableau is treating this as some sort of evaluative condition, but it doesn't seem to truncate values based upon this or perform those operations, at least with any syntax I tried. The fact that it recognizes that as some sort of function or evaluative condition is encouraging, at least.
Obviously a solution without having to create calculated fields is ideal (and mine is still only half a solution). I'd love to know if someone can get further than I did.
Thanks Ben. This is an absolute killer from a usability standpoint. It looks like the conditions as specified in the brackets are simply being ignored. I believe that the default behavior here is to Positive;Negative;Zero and it doesn't look like there is a way to override this as there is in Excel.
I would really love to hear how people attack this shortcoming because it is cropping up all over my dashboards and is really becoming a deterrent for user adoption. To leverage the vertical data structure required for many of Tableau's visualizations, it becomes necessary to use the same column for multiple data types (e.g. Absolute Sales Values and % of Total). There are workarounds (e.g. a value column per data format) but some sort of conditional formatting or even better the Thresholding features that Microstrategy offers would alleviate many many headaches for my users in Tableau.
2 of 2 people found this helpful
We can have several fields in Text. The attach has one per size. As usual, workarounds have their limits. In this case there are too many fields in the grand total. I have a feeling there is a workaround for this too, but don't know it.
What @kettan demoed is how I first deal with this situation, the fallback when that doesn't work is to build my own string formatting.
Thanks Jonathan. I hoped you would see this, because I know if anyone knows if a grand total can be squeezed out of this scenario, you would know it.
I was wondering if it would be possible to make the grand total with an extra sheet!? Somehow I believe so and will try now.
Thanks for the suggestion, I will give that a shot. The other related issue that always pops up is when I have a parameter toggling a metric between types of values (e.g. $'sand %'s). This is an extremely common scenario and one that becomes extremely painful to manage. I have seen the solutions like http://community.tableau.com/docs/DOC-5245 which works, but now requires users to maintain a multitude of different calculated fields with messy case statements for actual displaying in the viz vs. those used for labeling those data points. To compound this issue, you run into precision problems with converting floating point values to strings which requires even more workarounds.
It seems like some basic attention put towards implementing things like inherited default formatting or conditional number formatting would go a very long way. This is a basic requirement in other BI packages available (from Excel to Microstrategy) that is neglected here, causing an immense amount of user frustration.
Sorry for turning a question into a rant, and I really do appreciate the help with trying to find ways to make these dashboards more usable.
10 of 10 people found this helpful
Attached is version 2 with a separate sheet for the Grand Total.
Ps. As for conditional formatting, please consider up-voting
1 of 1 people found this helpful
The way to deal with the grand total in one sheet is to do the aggregation in the calc so each measure returns the value or Null, so < 1000 would be IF SUM([Value]) < 1000 THEN SUM([Value]) END.
The other related issue that always pops up is when I have a parameter toggling a metric between types of values (e.g. $'sand %'s)
I think the same method with multiple fields in Text can be used for this.
This. Is. Brilliant. Thank you for this. Using default number formatting on these fields is so much easier than manually specifying number formatting for each parameter case and managing that. Seriously, I don't know if I can rep you enough for this. Thank you both.
Thanks for the great solution!
But I ran into problem that if I use multiple measures as labels for one column (mark) for some combinations there will be a gap between labels.
Like you see below Par 5,6,7,8 all of them have gaps.
I was able to semi solve it by dragging % label inbetween other two, but then position of Numbers and % will alternate from case to case, which will be very much confusing for the user:
Can anybody think of some other solution that wouldn't make % and numbers change order (one always stays on top of the other).
How to implement the same in Chart Axis. Like if the value of chart varies from 0 to billion the the axis should display accordingly. Ex: 0 to K or 0 - Thousand - Million - Billion.
2 of 2 people found this helpful
Within Tableau 10.0 there is another solution.
Use the custom formatting, and I must admit that if the number is not consistently in the M(millions) or K(thousands), etc, then you will have to do additional work using calculated fields.
Here is goes.
Format you measure ->
"Custom" the number formatting ->
Enter the value based upon your number degree:
Billions -> #,##0,,,.0B;-#,##0,,,.0B
Millions -> #,##0,,.0M;-#,##0,,.0M
Thousands -> #,##0,.0K;-#,##0,.0K
Notice how the number of columns are changing here. Play around with the formatting. Good luck.