Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Johnsmith23
Frequent Visitor

Convert total revenue into K,M,B,T

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.

Screenshot_2023-05-17-18-44-08-005_com.skype.raider.jpg

Screenshot_2023-05-17-18-44-03-413_com.skype.raider.jpg

Screenshot_2023-05-17-18-44-13-406_com.skype.raider.jpg

3 REPLIES 3
Ajendra
Resolver I
Resolver I

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:

  1. Select the column or measure that you want to format.

  2. In the "Modeling" tab, go to the "Formatting" section.

  3. 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."

  4. 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:

  1. Select the column or measure that you want to format.

  2. In the "Modeling" tab, go to the "Formatting" section.

  3. Under the "Format" drop-down menu, select "Custom."

  4. 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"
  5. 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.

amitchandak
Super User
Super User

@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/

 

@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 🙏

Slicer-1.jpg

charts-1.jpg

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors