Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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! 😊
Solved! Go to 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.
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"))
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.
Thank you, @daXtreme! I'm using calculation groups for the display units now and it works.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
65 | |
63 | |
52 | |
37 | |
36 |
User | Count |
---|---|
82 | |
67 | |
61 | |
46 | |
45 |