How to Limit Visuals to Display Values in Millions Only?

[apohl1]

Hi all,

Does anyone know how to set a visual to automatically format values, but with a maximum limit of millions? I want the values to be displayed in millions and below, without switching to billions or trillions.

Thanks in advance for your help! 😊

[Cookistador]

Hi  @apohl1 

You can achieve that with dax,

In an example, I have the following table

Unit	1
Thousand	1000
Million	1000000
Billion	1000000000

To display the value in the format you need, You just have to create the following DAX measure

Formatted Value =

VAR CurrentValue = sum(TestTable[Value])

RETURN

    SWITCH(

        TRUE(),

        CurrentValue >= 1000000, FORMAT(CurrentValue / 1000000, "#,##0.00 M"),

        CurrentValue >= 1000 || CurrentValue <= -1000, FORMAT(CurrentValue / 1000, "#,##0.0 K"),

        FORMAT(CurrentValue, "#,##0")

    )

In you case, you just have to replace:
VAR CurrentValue = sum(TestTable[Value]) by the name of the measure you want to show

And this is the result

Do not hesistate to ask if you need more help

[apohl1]

Thank you! The measure works for a table but when changing the visual to a column chart it shows no data. Do you know why that is?

[Cookistador · Accepted solution]

To a column chart, it is normal that is not working

You have to keep a common scale for the y-axis

but you can use it as data label or tootltip

[apohl1]

Thank you for the suggestions. Ideally I would like to find a way to format the actual measure directly, to avoid duplications of the information in the visual for end users. Are you aware of any way to do it directly in the measure?

[v-hjannapu]

Hi @apohl1,

In Power BI there is no direct option to lock the axis only in millions. The axis units automatically adjust based on the data size, so when numbers go very high it switches to billions or trillions.

The reason your DAX measure works in table but not in column chart is because FORMAT() changes the value into text. Chart axis needs numbers, so it shows blank.

What you can do is:
      Keep your original measure numeric for the chart axis and in the formatting pane set Display units to Millions.
     Use the formatted measure with “M” only for data labels or tooltips, so users will still see values in millions.

This way chart will display properly and users can read values in the way you want.

If you are still facing any issues, please reach out here and we will be happy to help you.

Regards,
 Community Support Team.

[apohl1]

Thank you for explaining. In my case, I will leave the labels as they are and the end users will have to accept numbers shown in billions even if it's not ideal.

[v-hjannapu]

Hi @apohl1,

I would also take a moment to thank @Cookistador  , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Regards,
Community Support Team.