cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gjadal
Microsoft
Microsoft

Format currency to millions and thousands symbol based on the value

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?

 

1 ACCEPTED SOLUTION
v-yuta-msft
Community Support
Community Support

@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" ) )
)

Capture.PNG 

 

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.

View solution in original post

3 REPLIES 3
danielgajohnson
Helper I
Helper I

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
Community Support
Community Support

@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" ) )
)

Capture.PNG 

 

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.

View solution in original post

Anonymous
Not applicable

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" ) )
)

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!