Forum Discussion
Units shown on visuals
Can I set PowerBI to display numbers with commas at the top of the display? So 1 million would be displayed as 1'000 and 700k as 0'700.
Well you technically could, but then I believe it will be then considered a string as Power BI does not natively support a apostrophe as a separator.
As per the format you described, I believe you wanted it formatted based on a scale of a million. I have wriiten a basic structure, but you will need to build upon it for cases where more than 100 million or cases where it is less than 100k but it will give you a head start:Value with Apostrophe = SUBSTITUTE( FORMAT(SUM('Test Data'[Value]) / 1000000, "0.000"), ".", "'" )Here is the DAX to build the table to test it with:
Test Data = DATATABLE( "Category", STRING, "Value", INTEGER, "Description", STRING, { {"Sales", 1250000, "1.25 million"}, {"Marketing", 750000, "750 thousand"}, {"Operations", 2500000, "2.5 million"}, {"HR", 125000, "125 thousand"}, {"IT", 3750000, "3.75 million"}, {"Finance", 500000, "500 thousand"}, {"Research", 1875000, "1.875 million"}, {"Support", 625000, "625 thousand"}, {"Development", 4200000, "4.2 million"}, {"Training", 300000, "300 thousand"} } )Personally, I would recommend avoiding this but if you really need it, I hope this helps!
7 Replies
That is automatic for cards (Power BI chooses for you the best format based on the available space in the visual, sets to "auto"), and you can also choose to change this and decide the format you want and keep it ("none", "thousands" etc)
On visuals different from cards, you need to decide if you want to use a specifi format (it defaults to "none" but you can choose "none", "thousands" etc)
All is done through the format pane, for cards it is on the "callout values" section of the format pane, for other visuals under "specific column" section and then subsection "values"of the fomat pane
If this helped, please consider giving kudos and mark as a solution
me in replies or I'll lose your thread
Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page
Consider voting this Power BI idea
Francesco Bergamaschi
MBA, M.Eng, M.Econ, Professor of BI
Well you technically could, but then I believe it will be then considered a string as Power BI does not natively support a apostrophe as a separator.
As per the format you described, I believe you wanted it formatted based on a scale of a million. I have wriiten a basic structure, but you will need to build upon it for cases where more than 100 million or cases where it is less than 100k but it will give you a head start:Value with Apostrophe = SUBSTITUTE( FORMAT(SUM('Test Data'[Value]) / 1000000, "0.000"), ".", "'" )Here is the DAX to build the table to test it with:
Test Data = DATATABLE( "Category", STRING, "Value", INTEGER, "Description", STRING, { {"Sales", 1250000, "1.25 million"}, {"Marketing", 750000, "750 thousand"}, {"Operations", 2500000, "2.5 million"}, {"HR", 125000, "125 thousand"}, {"IT", 3750000, "3.75 million"}, {"Finance", 500000, "500 thousand"}, {"Research", 1875000, "1.875 million"}, {"Support", 625000, "625 thousand"}, {"Development", 4200000, "4.2 million"}, {"Training", 300000, "300 thousand"} } )Personally, I would recommend avoiding this but if you really need it, I hope this helps!
Hi leo124 ,
Thank you for reaching out to the Microsoft Community Forum.
Hi FBergamaschi , alish_b , Thank you for your prompt responses.
Hi leo124 , Could you please try the proposed solutions shared by FBergamaschi , alish_b . Please do let us know if you have any further queries.
Regards,
Dinesh
Hi leo124 ,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.
Regards,
Dinesh
Hi @leo124 ,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.
Regards,
Dinesh
Power BI uses locale settings for number formatting:
To get 1'000'000 style, change File → Options → Regional Settings or your Windows locale.
To get 0'700 style, create a DAX measure:
FormattedValue = FORMAT(DIVIDE(SUM('Table'[Amount]),1000), "0'000")
This converts numbers to thousands with a custom ' separator.
