topic I want units to be displayed properly in DAX Commands and Tips

I want units to be displayed properly

pborah — Mon, 24 Jun 2024 16:14:49 GMT

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.

Re: I want units to be displayed properly

jdbuchanan71 — Mon, 24 Jun 2024 16:22:16 GMT

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.

Re: I want units to be displayed properly

pborah — Mon, 24 Jun 2024 16:42:45 GMT

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.

Re: I want units to be displayed properly

jdbuchanan71 — Mon, 24 Jun 2024 20:54:48 GMT

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.

Re: I want units to be displayed properly

Anonymous — Tue, 25 Jun 2024 04:02:17 GMT

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.