Why is this RANKX not working?

[Remco1986]

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?

[Remco1986 · Accepted solution]

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)