Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I am developing a chart in which the accounts that will be viewed vary GREATLY by size and some will have sales in tens of millions while others in the low thousands; is there a way for number formats to automatically adjust to display a short num format?
I can do it manually by adding like "/1000" at the end of the expression and then in the number format adding a 'K' to the end so 10,000 would display as 10k but this will not be very dynamic when accounts go into the millions. For example a 10,000,000 account would show as 10,000k which is kind of confusing (ten thousand thousand).
Any help or tips are appreciated.
Thanks,
Stephan
Hi Stephan,
you can use an expression like this:
=If(Value > 1E6, Round(Value/1E6, 1) & 'M',
If(Value > 1E3, Round(Value/1E3, 1) & 'k',
Value))
But a standard format code would be better obviously.. Also, the division could lead into rounding issues.
- Ralf
Hey thanks for the quick reply!!
This may be a silly question but would your expression be used in my actual expressions section where I would replace 'Value' with my current calculation? Or would it go somewhere in the number formatting tab?
Thanks!
Correctly, just replace Value with your expression.
hmmm; I tried plugging it in and It's saying no data to display even though it says expression is OK.
My current expression is:
=If((sum($(vMetric))) > 1E6, Round((sum($(vMetric)))/1E6, 1) & 'M',
If((sum($(vMetric))) > 1E3, Round((sum($(vMetric)))/1E3, 1) & 'k',
(sum($(vMetric)))))
Where I used sum($(vMetric)) in place of Value because I am using buttons to change the metric (from dollars to quantity sold); could this be causing an issue?
Hm I took out the symbols ( & 'M') and it works now; I am using this in a line chart and adding the M makes the resulting value not a regular number and thus cannot really be measured. Now I need to find out how to add the M and K using the number tab I guess?
Try converting the result of the rounding to text using the text() formula
Sorry, just saw that you are using it in a line chart. Of course a text will not help anything there ...
But why don't you just use the default possibility of adding 'm' and 'k' from the numbers tab anyways?
Try using the dual function:
=If((sum($(vMetric))) > 1E6, dual(Round((sum($(vMetric)))/1E6, 1) & 'M', Round((sum($(vMetric)))/1E6, 1)),
If((sum($(vMetric))) > 1E3, dual(Round((sum($(vMetric)))/1E3, 1) & 'k', Round((sum($(vMetric)))/1E3, 1)),
(sum($(vMetric)))))
Hope this helps!
Unless I am crazy; that functionality does not seem to work for me. How is it correctly implemented?
I put a K in thousands symbol, M in million, and B in billions but it does absolutely nothing.
This has to do with the if-statement. Eliminate that and just put in sum($(vMetric)) and let the number tab take care of the rest. Than the 'k', 'm' and 'b' should appear. I have an example application for you, but still need to figure out how to upload it here ..
Here you go:
-- edit: This only shortens the axis though, not the numbers in the chart. --