cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Dynamic Formatting for Thousands & Millions

Hi all,

 

I wonder if anyone can suggest a means of using the custom format capabilities of PowerBI to replicate what in Excel would look like this:

 

[>=1000000] #,##0.00,,"m";[<1000000] #,##0.0,"k";General

 

Essentially what I'm after is for when the number is in the millions for it to be formatted with two decimal places and have an 'm' appended to the number, when it's in the thousands, a single decimal place and a 'k' appended. I'm aware that I could create a measure using the SWITCH function to do this, but wonder if anyone has come up with a custom format that does the same thing?

 

Thanks in advance,

 

SamB

5 REPLIES 5
Abhinav2194
New Member

Abhinav2194_0-1672213123391.png

In the above screenshot, you can see I have selected the measure - "Total Revenue".

Under the format Tab, the format Is General.

---- Switch the format to Currency.

---- Also adjust the decimal points to two places.

DONE.

Abhinav2194_1-1672213366276.png

Abhinav2194_2-1672213379722.png

Check the above screenshot. You can check the card which shows Monthly revenue and check the values as they add a "K" or "M" when required.

I

 

TrevLc
Frequent Visitor

I am looking for a solution to this too if anyone knows of one?

Rather than writing it into a measure, I would like to do it using the dropdown option to select custom format like you do in Excel

DanielG123
Regular Visitor

Hi, 

I have also encountered the same issue and was wondering has anyone found a solution for this yet. Even when i set the 

[>=1000000] #,##0.00,,"m";[<1000000] #,##0.0,"k";General

in excel and import into PowerBi, it will clear this.  

amitchandak
Super User
Super User
Anonymous
Not applicable

Hi,

Thanks for the reply - I think maybe you misunderstood the question slightly: I'm looking to use custom formatting in the Model view to do this rather than in a measure. The ultimate goal being that I can do a quick, non-destructive change to a number's format rather than having to go a write a measure everytime (as per the SWITCH suggestion in my original post). The RADACAD article is useful, but only really for date formats.

 

Custom format in Model viewCustom format in Model view

 

Thanks,

 

SamB

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.