I've seen similar questions asked previously in searches, but thought I would see if there have been any updates or ideas to fix this issue. I would like to use the following number format on a Sales field:

[>=1000000] $#.0,,"M";[>=1000]$#.0,"K"; $0

This will produce the following number formatting (note this is pulled directly from Excel using this number formatting):

1

-->

$1

10

-->

$10

100

-->

$100

1000

-->

$1.0K

10000

-->

$10.0K

100000

-->

$100.0K

1000000

-->

$1.0M

10000000

-->

$10.0M

100000000

-->

$100.0M

Tableau doesn't seem to accept this into its custom number formatting field and the closest that I can get is to specify the units ($) in the axis and let Tableau take care of it using its automatic number formatting. I was just wondering if anyone could weigh in on how to specify that "Automatic" number formatting in the custom field and pre-pend the dollar sign. Thanks for any help.

Great question. The closest I can seem to get is by using a calculation like this:

IF SUM([Sales]) >= 1000000 THEN SUM([Sales])/1000000

ELSE -SUM([Sales])/1000

END

with the following formatting:

$#.#M; $#.#K

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.

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.

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.

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.