cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors