Raw Data in Thousands (sometimes in Millions); How to "Scale" the Data Labels

[Terp]

I've asked this once before (and so many before me have, too) and a gentleman provided me with a workaround that works 80% of the time. 🙂

My problem:

• Assume my raw data are ALL in thousands of U.S. dollars (i.e., $1 = $,1000)
• I want my numbers stored in thousands to be expressed in millions, for example ($1,000 in the raw data = $1,000,000)
• These numbers are "scaled"(?) by many U.S. statistical agencies ("Millions of U.S. Dollars" means every $1,000 is a million)
• Toggling "Display in Million" under the format settings doesn't work , as 1,000 in raw data will be $0.0M because it's scaled

Sure, I could create a quick calc for every single field, and I have many, that multiplies/scales it by multiplying by 1000...but that starts getting unwieldy.

The aforementioned workaround was to create a 'custom' value like I do in Excel ($0.0,,"M") for each field. So my numbers stored in thousands would be truncated to 1 digit + a decimal and add the "M" to make it look like $1.0M when the raw number is $1,000.

This mostly works, but as numbers get small (COVID declines) or huge gains (post-COVID), we fluctuate in an out of thousands, millions, and billions and back to having wonky $0.0T type of displays, if that makes sense. 🙂

SOOOOOOO, I am wondering if there is an elegant (DAX?) solution to scale the custom labels using some logic, per se?

Example:

If value is greater than 1 and less than 1,000, use "$0.0,,"M"  

If value is greater than 1,000 but less than 1,000,000 use "$0.0,,"B"

If value is greater than 1,000,000 use "$0.0,,"T"

...

It would be nice if there were a scale slider to select whether or not the numbers are real, whole numbers or have been scaled into, say, "Millions of U.S. Dollars" like big data publishers often do...without having to create 2x the number of fields merely to scale the data to get the proper "M", "B", and "T" behind the raw number.

What say you? 😉

[Terp]

Thank you!  I definitely will work on implementing this in a few visuals for sure...sounds like exactly what I wanted to do. Many, many thanks!

However, I got to thinking...

• So I will need to do a new measure for every single variable in my spreadsheet?!? If I am doubling the columns using this approach, wouldn't it be simpler if I created double the columns and multiplied the number expressed in thousands by x*1000 and just use the new figure? 🙂

• AND, I think I will still have issues with the axis labels, no? 

I'll try to figure this out today and report back.  Thanks again!

[v-xulin-mstf]

Hi @Terp

Is your issue solved?

If you still have some question, please don't hesitate to let me known.‌‌

Best Regards,

Link

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!

[v-xulin-mstf]

Hi @Terp,

In this case, the measure returns in text format, so it will still have issues with the axis labels.

By double column do you mean one column for value and one column for 'T', 'B', 'M'?

That's a good idea, in that case, it will not have issues with the axis labels.

Looking forward your good news! 😉

Best Regards,
Link

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[v-xulin-mstf]

Hi @Terp

You can use CONCATENATE function to meet your need.

Try measure like:

Measure = 
SWITCH(TRUE(), 
    [value]>1&&[value]<1000,        CONCATENATE(DIVIDE([value],10),"M"),
    [value]>1000&&[value]<1000000,  CONCATENATE(DIVIDE([value],10),"B"),
    [value]>1000000,                CONCATENATE(DIVIDE([value],10),"T"),
    
   )

Best Regards,
Link

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Terp]

Sorry, all data for this exercise are in millions...ended up using $0,.0"B" to get it to display properly...still doesn't seem like the right way to address this (and will get wonky when the numbers get into the millions and trillions)....but enough of a band-aid that I can move on, I guess.

I'm too tired to think through what will happen when I start adding more calculations in and I am cheating the system with a custom format, but I'll cross that bridge when I get there... 😞

Scratch that...just learned changing the format changes the underlying data, not just the visual display, so that's not going to work...