I want units to be displayed properly

[pborah]

Case in point -

The values above have not reached millions yet and I want powerBI to stop representing it in decimal digit millions and show it as thousands instead. However when the values do reach millions, I want powerbi to switch from using thousands to millions. Currently it's in auto. But if I was to set it myself, it could only be one of thousands or millions or billions. I'm not sure how the custom option will work here. 

Thanks.

[Anonymous]

Hi @pborah ,

I create a table and a Stacked bar chart.

Then you can click on this button below.

Best Regards

Yilong Zhou

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

[jdbuchanan71]

No because the amounts are dynamic based on filtering and calculated columns are are only refreshed when the model is refreshed.  I have this measure running in a model that has 60 million rows in the main table and it runs fine.  The number it is formatting is just a SUM measure and that should run very quickly even over millions of rows.

[jdbuchanan71 · Accepted solution]

@pborah 

Your best bet is to write a measure that handles the formatting exactly the way you want, something like this.

Paid Amount Label =
VAR _PaidAmount = [Paid Amount]
RETURN
SWITCH (
    TRUE(),
    _PaidAmount > 1000000, FORMAT ( _PaidAmount, "#,#,,.0 M"),
    _PaidAmount > 1000, FORMAT ( _PaidAmount, "#,#,.0 K"),
    FORMAT ( _PaidAmount, "#,#.00")
)

Then use that measure as your data label for the chart.

[pborah]

thanks @jdbuchanan71 . Will this work for a calculated column? the dataset has over a few million rows and if I can get away with just modifying the dax code for the calculated column that gives me these numbers, i'd rather not create a separate measure so I can keep the overhead to a minimum.