Units shown on visuals

[leo124]

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.

[Shahid12523]

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.

[v-dineshya]

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

[v-dineshya]

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

[v-dineshya]

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

[v-dineshya]

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

[alish_b · Accepted solution]

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!

[FBergamaschi]

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