Today I challenged myself to fix an issue that I had in one of my dashboards: dynamic number formatting.
The problem: I have a dashboard that contains more than 4 million download records. Users ask me to display the sum of all downloads as millions but when they drill down to a specific product they prefer to see the sane number as thousands.
Originally I left the numbers formatted as millions so I got this display:
But when I drilled down to a product with few downloads, then it got ugly:
So I asked around and Andy Kriebel gave me this amazing tip:
And it was so easy to find the solution then. And basically if I had put more though on it myself maybe I could have done it. The solution that I found easier to implement is basically creating 3 calculated fields:
< 1000 Downloads : IF SUM([Downloads]) < 1000 THEN SUM([Downloads])END
K Downloads : IF SUM([Downloads]) >= 1000 AND SUM([Downloads]) < 10000000 THEN SUM([Downloads]) END
M Downloads : IF SUM([Downloads]) >= 1000000 THEN SUM([Downloads]) END
Next I had to edit the format of the numbers for K Downloads and M Downloads fields. When done I just dragged the 3 fields to Text and I was done!
No more 0.00M to show to users and force them to hover the ball to display the data.
I hope this simple solution can help you as much as it did help me 🙂