Format currency to millions and thousands symbol based on the value

[gjadal]

I have a column in a table matrix visual which contains different types of data like Currency, Units, %.

Is there a way to dynamically set the format of the values using Format.

For example if it is currency and value is 100, I need $100 but if the value is bug like  10120 then i want it displayed as $10.12K

Similarly for Units I need the K and M symbol if the value gets larger.

Also wanted a better way to set the format to a % or $ based on the type. Is there a ready function available or do I need to write DAX with all the SWITCH statements?

[guptav13]

If I am exporting it to excel I can not work with these formatted numbers as they are string now, is there a way to get the output as numbers

[danielgajohnson]

Has anyone found a way to do this that allows for aggregation functions on that column? I can't SUM the resulting column because it's now a string.

[v-yuta-msft · Accepted solution]

@gjadal,

I'm afraid currently there's no such option in Currency Format in the Modeling pane. As a workaround, you can use switch function to achieve that.

Column = 
SWITCH (
    TRUE (),
    Table1[Currency] < 1000, CONCATENATE ( "$", Table1[Currency] ),
    Table1[Currency] >= 1000, CONCATENATE ( "$", CONCATENATE ( Table1[Currency] / 1000, "K" ) ),
    Table1[Currency] >= 1000000, CONCATENATE ( "$", CONCATENATE ( Table1[Currency] / 1000000, "M" ) )
)

 

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[shmmini]

Looks to me that the clauses inside the Switch have to be in the opposite Order, Especially for conditions 2 and 3. The way it is ordered here, you'll never get to condition 3 as conditions 1 and 2 cover all the possible numbers

[Anonymous]

Good stuff and here's a little improvement on the value range to which a conversion applies and to round to 2 decimals only

Column =

SWITCH (

    TRUE (),

    Table1[Currency] < 1000, CONCATENATE ( "$", Table1[Currency] ),

    AND(Table1[Currency] >= 1000, Table1[Currency] < 1000000), CONCATENATE ( "$", CONCATENATE (  ROUND(Table1[Currency] / 1000, 2), "K" ) ),

    Table1[Currency] >= 1000000, CONCATENATE ( "$", CONCATENATE ( ROUND(Table1[Currency] / 1000000, 2), "M" ) )

)

[chizuml_ds]

Great program, but now that we have a string, how do we sort the K and M numbers to make sense?  Thx

[Ayyappa5678]

Hi,

Is above is resolved or not?
If resolved kindly please share me the resolution here.
Thanks