Forum Discussion
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 |
| Attribute | Section 2 | Question 3 | 2610.00 | 2950.00 |
| Attribute | Section 3 | Question 4 | 2015.00 | 2080.00 |
| Attribute | Section 3 | Question 5 | 2925.00 | 2950.00 |
| Attribute | Section 4 | Question 6 | 2845.00 | 2930.00 |
| Attribute | Section 4 | Question 7 | 4395.00 | 4910.00 |
| Attribute | Section 4 | Question 8 | 785.00 | 810.00 |
| Attribute | Section 4 | Question 9 | 1890.00 | 1980.00 |
| Attribute | Section 4 | Question 10 | 1104.00 | 1122.00 |
| Attribute | Section 4 | Question 11 | 1882.50 | 1945.00 |
| Attribute | Section 5 | Question 12 | 507.50 | 545.00 |
| Attribute | Section 5 | Question 13 | 2267.50 | 2405.00 |
| Attribute | Section 5 | Question 14 | 2640.00 | 2745.00 |
| Attribute | Section 5 | Question 15 | 2547.50 | 2950.00 |
| Attribute | Section 5 | Question 16 | 5000.00 | 5820.00 |
| Attribute | Section 5 | Question 17 | 1295.00 | 1410.00 |
| Attribute | Section 6 | Question 18 | 9840.00 | 11800.00 |
| Attribute | Section 6 | Question 19 | 960.00 | 1095.00 |
| Attribute | Section 6 | Question 20 | 400.00 | 480.00 |
| Attribute | Section 6 | Question 21 | 132.50 | 155.00 |
| Attribute | Section 7 | Question 22 | 352.50 | 425.00 |
| Attribute | Section 7 | Question 23 | 1685.00 | 1980.00 |
| Attribute | Section 8 | Question 24 | 1975.00 | 2420.00 |
| Master | Total Result | Total Result | ||
| Section | Section 1 | 1790.00 | 1820.00 | |
| Section | Section 2 | 3802.50 | 4405.00 | |
| Section | Section 3 | 4885.00 | 5030.00 | |
| Section | Section 4 | 12901.50 | 13697.00 | |
| Section | Section 5 | 13912.50 | 15875.00 | |
| Section | Section 6 | 11267.50 | 13530.00 | |
| Section | Section 7 | 2037.50 | 2405.00 | |
| Section | Section 8 | 1975.00 | 2420.00 |
and a measure to calculate pass rate:
(%) Pass Rate = iferror(sum('QA Data'[Points])/sum('QA Data'[Max Points]),BLANK())
Now, I can display a simple table visual with the Pass Rate per Attribute (with a visual filter on Attribute Category = Attribute) and a table visual with the Pass Rate per Section (with a visual filter on Attribute Category = Section).
Now, I need to color-code the top 5 and bottom 5 attributes. After searching the web, the best approach seems to be to create a rank measure, and then look at rank 1 through 5 for the top 5, and rank 19 through 24 for the bottom 5 (cause there are 24 arrtibutes).
However, every different RANKX approch I have used so far results in either rank 1 for all attributes, or rank with very high numbers (matches rows of data, example the highest pass rate has rank 1, then the next attribute has rank 388 meaning there are 387 rows for the same attribute that got rank 1).
All I need is a rank 1 through 24. What am I doing wrong?
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)
1 Reply
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)