Forum Discussion

Remco1986's avatar
Remco1986
Helper I
5 years ago
Solved

Why is this RANKX not working?

I have data as per below: Attribute Category Section Attribute Points Max Points Attribute Section 1 Question 1 1790.00 1820.00 Attribute Section 2 Question 2 1315.00 1455.00 ...
  • Remco1986's avatar
    5 years ago

    After some troubleshooting, I have found the solution. Let me explain:

    I have a 2nd table with the sortOrder of the questions. In my main data, I have added the sortorder using the RELATED() function. I then sorted the Attribute text by the sortOrder.

     

    This is what broke the RANKX. All I needed to do was to include the sortOrder in the RANKX function.

     

    So this: 

     

    RANKX(all('QA Data'[Attribute]),calculate(sum('QA Data'[Points])/sum('QA Data'[Max Points])),,DESC,dense)

     

     

    Should be changed to this:

     

    RANKX(all('QA Data'[Attribute],'QA Data'[sortAttribute]),calculate(sum('QA Data'[Points])/sum('QA Data'[Max Points])),,DESC,dense)

     

    Or shorter, I could use the Pass Rate measure instead of the CALCULATE(SUM()/SUM())

     

    RANKX(all('QA Data'[Attribute],'QA Data'[sortAttribute]),[(%) Pass Rate],,DESC,dense)