topic Re: Convert total revenue into K,M,B,T in DAX Commands and Tips

Convert total revenue into K,M,B,T

Johnsmith23 — Wed, 17 May 2023 14:17:16 GMT

I want convert Total revenue to K,M,Bn,T. But could not convert properly. when convert into K its showing me M and vice versa and I want the value in data label to be visible in comma, I have attached the measure I'm using.

Re: Convert total revenue into K,M,B,T

amitchandak — Wed, 17 May 2023 15:29:52 GMT

Johnsmith23 , have you tried the new dynamic format option from April 2023

Check the below code in link

SWITCH ( TRUE (), SELECTEDMEASURE () < 1000, “$#,##0”, SELECTEDMEASURE () < 1000000, “$#,##0,.0K”, “$#,##0,,.0M” )

- https://powerbi.microsoft.com/en-us/blog/deep-dive-into-the-new-dynamic-format-strings-for-measures/

Re: Convert total revenue into K,M,B,T

Ajendra — Wed, 17 May 2023 20:13:22 GMT

In Power BI, you can convert values to different units such as K (thousands), M (millions), Bn (billions), and T (trillions) using the "Format" options. Here's how you can do it:

Select the column or measure that you want to format.
In the "Modeling" tab, go to the "Formatting" section.
Under the "Format" drop-down menu, choose the desired format for your values. For example, if you want to convert values to millions, select "Millions."
Power BI will automatically format the values in the selected column or measure based on your chosen format. The values will be displayed in a shortened format with a corresponding unit (e.g., 1.5M for 1.5 million).

Note: If you want to customize the format or use a different unit, you can follow these steps:

Select the column or measure that you want to format.
In the "Modeling" tab, go to the "Formatting" section.
Under the "Format" drop-down menu, select "Custom."
In the "Format code" field, enter the desired format code. Here are some examples:
- To display values in thousands (K): "#,##0,K"
- To display values in millions (M): "#,##0.0,,M"
- To display values in billions (Bn): "#,##0.0,,,Bn"
- To display values in trillions (T): "#,##0.0,,,,T"
Power BI will format the values in the selected column or measure according to your custom format code. The values will be displayed with the specified unit (e.g., 1.5M for 1.5 million).

By using these formatting options, you can easily convert values to different units in Power BI.

Re: Convert total revenue into K,M,B,T

Johnsmith23 — Thu, 18 May 2023 08:08:59 GMT

amitchandak @Im using Slicer to display values

My measure is Total Income = SWITCH(TRUE(), SELECTEDVALUE('Display units'[Amount Units]) = "Millions" ,[Total Revenue]/1000000, SELECTEDVALUE('Display units'[Amount Units]) = "Thousands" , [Total Revenue]/ 1000,

SELECTEDVALUE('Display units'[Amount Units]) = "Billion" ,[Total Revenue], SELECTEDVALUE('Display units'[Amount Units]) = "Trillion" , [Total Revenue]/1000000000000, SELECTEDVALUE('Display units'[Amount Units]) = "Original Value",[Original Revenue], [Total Revenue] )

For the Total Revenue I have measure SUM('Financials Sample Data'[Jan]) + SUM('Financials Sample Data'[Feb]) + SUM('Financials Sample Data'[Mar]) + ..... it goes on till (Dec)

In the total income Measure if I use FORMAT([Total Revenue], "$#,##0,,.0M") instead of[Total Revenue]/1000000

No value is being Displayed.

It's Been few days every since I have Started learning Power Bi. So Please forgive me If I have asked some blunt question

Thanks 🙏