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
Anonymous
Not applicable

Sort By Column changes the Measure results

Hi there,

 
I could really do with some help!
 
In Power BI Desktop, I would like to use the Sort by Column feature to re-arrange the order of one of the dimensions of my matrix table and associated 100% stacked column chart.
 
BEFORE:
Before using it, the data looks (correctly) like this. 
 
Before.JPG
 
It uses measures that, in the denominator (PWR), strips off the column filter using ALL() to derive the totals, as follows:
 
 
Percentage Attainment % = DIVIDE([PAC2],[PWR],0)

PAC2 = IF(HASONEVALUE(AssessData[Term]) && HASONEVALUE(AssessData[Subject]) ,
CALCULATE(COUNTA(AssessData[Assessment]),
AssessData[Assessment]<>""
),
0
)

PWR = IF(HASONEVALUE(AssessData[Term]) && HASONEVALUE(AssessData[Subject]) ,
CALCULATE(
COUNTA(AssessData[Assessment]), ALL(AssessData[PerCentAttainName]),
AssessData[Assessment]<>""
),
0
)
 
THEN:
However, I want to sort the Levels (Above, Below, Developing, etc) in non-alphabetical order and so I sorted the column 'PerCentAttainName' by another column 'PerCentAttainID'. These columns are calculated columns, picking up their values from a lookup table. PerCentAttainID holds integer values to organise the sort.
 
AFTER:
When I turn on the Sort By Column feature, it changes all the values to this (as if the ALL() function did not exist as the values in PWR denominator measure are the same as PAC2 numerator):
 
After.JPG
As you can see, the sort order is now as I would like, but the measure calculation has changed!
 
And then when I turn the Sort By Column feature off again, it reverts back to the correct sort order!
 
THOUGHTS:
I don't think the measures are wrong - ALL() should work this way, whether or not the column is sorted by another column or not. However, I am relatively new to DAX, so I could be fundamentally misunderstanding things.
 
I thought perhaps there was a bug in using RELATED(), so I populated the source table with actual data rather than looked-up data. Unfortunately I got the same behaviour.
 
Any help gratefully received! Thanks.
 
 
1 ACCEPTED SOLUTION
Anonymous
Not applicable

I have found the solution (if you are interested!) - a blog article posted by Chris Webb (well done, Chris!) - https://blog.crossjoin.co.uk/2015/12/15/power-bi-desktop-sort-by-column-and-dax-calculations-that-us...

 

I removed the measure [PWR] and [Percentage Attainment %], replacing the latter with:

 

PA2 = DIVIDE([PAC2], 
CALCULATE([PAC2],
ALL(AssessData[PerCentAttainName],AssessData[PerCentAttainID])
)
,0)

The percentages now flow through irrespective of whether the Sort By Column function has been applied. Phew.

View solution in original post

4 REPLIES 4
historianoh005
New Member

Faced this issue while applying Sort by on a text column having both numbers and text that needs to sort by a custom order. This method work with ALL(column,sortorder) applied. Thanks! 

Anonymous
Not applicable

I have found the solution (if you are interested!) - a blog article posted by Chris Webb (well done, Chris!) - https://blog.crossjoin.co.uk/2015/12/15/power-bi-desktop-sort-by-column-and-dax-calculations-that-us...

 

I removed the measure [PWR] and [Percentage Attainment %], replacing the latter with:

 

PA2 = DIVIDE([PAC2], 
CALCULATE([PAC2],
ALL(AssessData[PerCentAttainName],AssessData[PerCentAttainID])
)
,0)

The percentages now flow through irrespective of whether the Sort By Column function has been applied. Phew.

Woah, I had the same issue but with ALLSELECTED() instead of ALL().

Works in ALLSELECTED() too!

Thank you.

@Anonymous I ran into this issue and your post helped me fix my problem and for that I want to thank you!

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.