Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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
Power BI can now do this. It is called dynamic format strings
Create dynamic format strings for measures in Power BI Desktop - Power BI | Microsoft Learn
Hi @TrevLc Thanks for pointing this out. But have you been able to make it work with the "commas" to divde the number by 1000, 1000000 etc.? I tried but it does not seem like it is implemented in the Dynammic format strings?
hi i am wondering if someone could helpme. i tried to change the format to currency and seted up with two decimal places. i got everything righ in other chart but not this multi-row card. I don't know why.
If these are measures, make sure it is set on the measure itself
I have set this and it seems to work for me
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.
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
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
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.
Refer
https://radacad.com/custom-format-fields-and-measures-in-power-bi
https://community.powerbi.com/t5/Desktop/Measures-editing-number-formats/td-p/240597
Measure = FORMAT( sales[sales], $#,##0;($#,##0) )
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601
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.
Thanks,
SamB
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
87 | |
87 | |
81 | |
63 | |
49 |
User | Count |
---|---|
121 | |
109 | |
81 | |
67 | |
67 |