Sort By Column changes the Measure results

[Anonymous]

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. 

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):

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.

[historianoh005]

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 · Accepted solution]

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.

[garvitgupta]

I was looking for a solution for this, and I never imagined it could be such a small thing making my life difficult. 😅

Thanks for sharing.

[JFarq]

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

Works in ALLSELECTED() too!

Thank you.

[yvese]

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