How to sort text values from measure by their number values?

[Max_]

Hi everyone,

In one of my Power BI reports, I implemented slicer with different formatting options (e.g. revenue in thousands vs. million vs. default) and use a measure to format the values in a matrix accordingly. This is the relevant function in the mentioned measure:

FORMAT(
    SUM([Revenue]),
    SELECTEDVALUE('Number Format'[Value])
)

This works fine and the users can select the format they would like to use. Unfortunately, when they try to sort the matrix by revenue, it gets sorted alphabetically as the output of the FORMAT function is a text:

Do you have any solution for this? How can I enable my users to sort the column by the number values?

Thank you! 😊

[jjpais]

I know this specific question was solved with using Calculation Groups, but I also have a solution for this inside a measure.  In my specific use case this was for formatting a currency value for use in tables/matrix visuals where the auto units is unavailable (side note that really should be fixed).  I came across a partial solution to this where a 3rd party website showed using a blank white space repeat in front of a text return in order to sort the values based on criticality (bad, warning, good) where the good values were in the middle of the range similar to a high and low level pressure alarm would work on a pump.  

Here is an example of formatting the numbers using the repeat, unichar, and format functions to format the values based on the size of the value calculated.

Amount Formatted = 
var ttl = CALCULATE(SUMX('Table',[Value_Column]))
var attl = ABS(CALCULATE(SUMX('Table',[Value_Column])))
var spcs = SWITCH(TRUE(), attl < 10, 0,ROUNDUP(VALUE(LOG10(attl)),0))
return 
SWITCH(TRUE(),
    attl / 1000000 > 1000, FORMAT(ttl / 1000000000, "$" & REPT(UNICHAR(8203), spcs) &"  #,###.##0B"),
    attl / 1000 > 1000, FORMAT(ttl / 1000000, "$" & REPT(UNICHAR(8203), spcs) &"  #,###.##0M"),
    attl > 1000,  FORMAT(ttl / 1000, "$" & REPT(UNICHAR(8203), spcs) &" #,###.#0K"), 
    ttl == 0 || ISBLANK(ttl), BLANK(), FORMAT(ttl, "$" & REPT(UNICHAR(8203), spcs) &"  #,###.#0"))

[daXtreme · Accepted solution]

FORMAT should not be used on numerical values which you want to sort because it returns text. If you want to implement formatting properly and be able to switch formats using a slicer, you have to use CALCULATION GROUPS. No way around this, I'm afraid. But this requires some knowledge. Please look on YT for videos that demonstrate this. There are plenty of them.

[Max_]

Thank you, @daXtreme! I'm using calculation groups for the display units now and it works.