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?
Go to Solution.
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.
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.
View solution in original post
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.
Good stuff and here's a little improvement on the value range to which a conversion applies and to round to 2 decimals only
Check out the News & Announcements to learn more.
Congratulations, the new Super User Season 2 for 2021 has started!
Check out the new Power Platform Community Connections gallery!