1 2 Previous Next 17 Replies Latest reply on Sep 27, 2018 8:40 AM by Monisha Ramesh

    Custom Number Formatting ($K,$M)

    Michael Carrico

      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.

        • 1. Re: Custom Number Formatting ($K,$M)
          Ben Neville

          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.

          • 2. Re: Custom Number Formatting ($K,$M)
            Michael Carrico

            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.

            • 3. Re: Custom Number Formatting ($K,$M)
              kettan

              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.

               

              thread 155537 Custom Number Formatting ($K,$M).png

              2 of 2 people found this helpful
              • 4. Re: Custom Number Formatting ($K,$M)
                Jonathan Drummey

                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.

                 

                Jonathan

                • 5. Re: Custom Number Formatting ($K,$M)
                  kettan

                  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.

                  • 6. Re: Custom Number Formatting ($K,$M)
                    Michael Carrico

                    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.

                    • 7. Re: Custom Number Formatting ($K,$M)
                      kettan

                      Attached is version 2 with a separate sheet for the Grand Total.

                      thread 155537 Custom Number Formatting ($K,$M) v2.png

                       

                      Ps. As for conditional formatting, please consider up-voting

                       

                      Conditional Formatting of Measure Names/Measure Values

                      10 of 10 people found this helpful
                      • 8. Re: Custom Number Formatting ($K,$M)
                        Jonathan Drummey

                        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.

                         

                        Jonathan

                        1 of 1 people found this helpful
                        • 9. Re: Custom Number Formatting ($K,$M)
                          kettan

                          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.

                          • 10. Re: Custom Number Formatting ($K,$M)
                            Michael Carrico

                            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.

                            • 11. Re: Custom Number Formatting ($K,$M)
                              Egor Kovalenko

                              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).

                               

                              Thanks!

                              • 12. Re: Custom Number Formatting ($K,$M)
                                gowtham subramaniam

                                Hi All,

                                 

                                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.

                                1 of 1 people found this helpful
                                • 13. Re: Custom Number Formatting ($K,$M)
                                  JEFFREY PENNER

                                  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 ->

                                  demo1.png

                                   

                                  "Custom" the number formatting ->

                                  demo2.png

                                   

                                  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.

                                   

                                  Jeffrey

                                  2 of 2 people found this helpful
                                  • 14. Re: Custom Number Formatting ($K,$M)
                                    Trevor McCormick

                                    If you have a limited number of measures there is an easier way to do this using calculated fields.

                                    For example if I have Sales as a measure, I can create a calculated metric that shows the value as a string in whichever format I want.

                                    1 2 Previous Next