Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

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

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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-use-the-all-function/

     

    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.

    • yvese's avatar
      yvese
      Frequent Visitor
      8 years ago

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

    • JFarq's avatar
      JFarq
      Helper I
      3 years ago

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

      Works in ALLSELECTED() too!

      Thank you.

    • garvitgupta's avatar
      garvitgupta
      New Member
      9 months ago

      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.

  • historianoh005's avatar
    historianoh005
    New Member
    2 years ago

    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!