Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Max_
Frequent Visitor

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

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:

Max__1-1669107587272.png

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

Thank you! 😊

 

1 ACCEPTED SOLUTION
daXtreme
Solution Sage
Solution Sage

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.

View solution in original post

3 REPLIES 3
jjpais
New Member

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
Solution Sage
Solution Sage

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_
Frequent Visitor

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.