cancel
Showing results 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

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:

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
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.

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

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.

Frequent Visitor

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

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Power BI Monthly Update - July 2024

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

Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors