CODEWITHSUNDEEP
powerbipowerbi-desktop
AzUser1
AzUser1

Reputation: 193

Power BI: Add thousand separator using dax to matrix

I have the following column with two different kind of data formats. I want to show it in a matrix visual and add thousand separators using dax. I know it's not ideal but due to different requirements it has to be like this.

Category Amount
Euro 1000
Total Stock 20000
Euro 500
Total Stock 4432423
Euro 23132
Total Stock 23423
Euro 3232
Total Stock 433
Euro 42424
Total Stock 12312
Euro 4545
Total Stock 32233

I created a measure for changing the format like the following:

Measure_Amount = 
Var varAmount = sum(table[Amount])
RETURN
SWITCH (
SELECTEDVALUE ( 'table'[Category] ),
"Total Stock", Format(varAmount, "#;-#;#"),
"Euro", Format(varAmount, "#€;-#€;#€"))

In short, it will change the format based on the Category column but it will be formated as text. It will show like this in Power BI. I have added the Amount column as row just to show the difference.

enter image description here

I would like to add thousand separators in the measure but I don't know how? Can anybody help? The default way to click it in Power BI doesn't work since the measure is formatted as text now.

Upvotes: 0

Views: 5316

Answers (2)

jezza_bro
jezza_bro

Reputation: 51

With the new power bi feature for Dynamic format strings there is a better solution now (although its currently still in preview)

https://learn.microsoft.com/en-nz/power-bi/create-reports/desktop-dynamic-format-strings

So you could add a dynamic format string Basically the same as Peters solution except you just

SWITCH (
    SELECTEDVALUE ( 'table'[Category] ),
    "Total Stock", "#,###",
    "Euro", "#,###€","#,###"
)

Peters solution would mean the measure data type would be a string which is not good when aggregating.

So by using the dynamic format string it would keep it a number and that way it would behave better when using the measure in different aggregation levels

Upvotes: 0

Peter
Peter

Reputation: 12335

How about adding the thousands separator to the format strings?

Formatted_Amount = 
VAR varAmount =
    SUM ( 'table'[Amount] )
RETURN
    SWITCH (
        SELECTEDVALUE ( 'table'[Category] ),
        "Total Stock", FORMAT ( varAmount, "#,###" ),
        "Euro", FORMAT ( varAmount, "#,###€" )
    )

enter image description here

Upvotes: 1

Related Questions