Prevent RANKX from changing when slicer is applied

[stbrooks13]

I am ranking our target schools by their school level. The rankings appear how they should in a grid view with all schools selected. However, when a single school is selected in the slicer, the ranking changes.

For instance, Appalachian State University is ranked #1 in the national school level. However, when I select Appalachian State University from the school list (national school level is also selected), its ranking shows as #2 .

The formula I'm using is:

Rank Overall = IF(AND(HASONEVALUE(School[School Level]),NOT(ISBLANK([Rank Total]))),RANKX(ALL(School[School]),[Rank Total],,ASC,DENSE),IF(AND(HASONEVALUE(School[School Level]),ISBLANK([Rank Total])),CALCULATE([Count of Schools],ALLEXCEPT(School,School[School Level])),BLANK()))

Where Rank Total is a sum of all leading indicator rankings using the same ranking format above.

 

[gazpage]

The [Rank Total] will still be calculated in the current filter context, so will presumably be returning 0 for all the other schools. You need to put it, or the ranks, inside a calculate with ALL( Schools )

 

[stbrooks13]

Thank you, but when I do this, it returns 1 for all schools. Here is more context from my formulas (I also removed HASONEVALUE). I’m ranking a combined total rankings of 12 leading indicators. Please help!

Rank Overall = IF(NOT(ISBLANK([Rank Total])),RANKX(ALL(School[School]),[Rank Total],,ASC,DENSE),CALCULATE([Count of Schools in Level]))
Rank Total = [Rank Effort]+[Rank Cost/Hire]+[Rank Quality]+[Rank Hires]

Rank Effort = IF(NOT(ISBLANK([Total Effort])),RANKX(ALL(School[School]),[Total Effort],,ASC,DENSE),CALCULATE([Count of Schools in Level]))
Total Effort = [Rank Event Success]+[Rank OCI Success]+[Rank OV Success]
Rank Event Success = IF(NOT(ISBLANK([Event Success])),RANKX(ALL(School[School]),[Event Success],,ASC,DENSE),CALCULATE([Count of Schools in Level]))
Rank OCI Success = IF(NOT(ISBLANK([OCIs])),RANKX(ALL(School[School]),[OCI Success],,DESC,DENSE),CALCULATE([Count of Schools in Level]))
Rank OV Success = IF(NOT(ISBLANK([OVs])),RANKX(ALL(School[School]),[OV Success],,DESC,DENSE),CALCULATE([Count of Schools in Level]))

Rank Cost/Hire = IF(AND(NOT(ISBLANK([Cost/Hire])),NOT(ISBLANK([Offers Accepted]))),RANKX(ALL(School[School]),[Cost/Hire],,ASC,DENSE)-1,CALCULATE([Count of Schools in Level]))

Rank Quality = IF(NOT(ISBLANK([Total Quality])),RANKX(ALL(School[School]),[Total Quality],,ASC,DENSE),CALCULATE([Count of Schools in Level]))
Total Quality = [Rank GPA]+[Rank Perf]+[Rank Attrition]
Rank GPA = IF(NOT(ISBLANK([Avg GPA])),RANKX(ALL(School[School]),[Avg GPA],,DESC,DENSE),CALCULATE([Count of Schools in Level]))
Rank Perf = IF(NOT(ISBLANK([Avg Perf])),RANKX(ALL(School[School]),[Avg Perf],,DESC,DENSE),CALCULATE([Count of Schools in Level]))
Rank Attrition = IF(NOT(ISBLANK([Associates for Attrition])),RANKX(ALL(School[School]),[Attrition],,ASC,DENSE),CALCULATE([Count of Schools in Level]))

Rank Hires = IF(NOT(ISBLANK([Total Hires])),RANKX(ALL(School[School]),[Total Hires],,ASC,DENSE),CALCULATE([Count of Schools in Level]))
Total Hires = [Rank Interns]+[Rank Intern Acceptance]+[Rank Assoc]+[Rank Assoc Acceptance]+[Rank Intern Conv]
Rank Interns = IF(NOT(ISBLANK([Interns])),RANKX(ALL(School[School]),[Interns],,DESC,DENSE),CALCULATE([Count of Schools in Level]))
Rank Intern Acceptance = IF(NOT(ISBLANK([Interns])),RANKX(ALL(School[School]),[Intern Acceptance],,DESC,DENSE),CALCULATE([Count of Schools in Level]))
Rank Assoc = IF(NOT(ISBLANK([Associates])),RANKX(ALL(School[School]),[Associates],,DESC,DENSE),CALCULATE([Count of Schools in Level]))
Rank Assoc Acceptance = IF(NOT(ISBLANK([Associates])),RANKX(ALL(School[School]),[Assoc Acceptance],,DESC,DENSE),CALCULATE([Count of Schools in Level]))
Rank Intern Conv = IF(NOT(ISBLANK([Interns])),RANKX(ALL(School[School]),[Intern Conversion],,DESC,DENSE),CALCULATE([Count of Schools in Level]))

 

[stbrooks13]

I'm still trying to figure this out.

Do you mean: Rank Overall = RANKX(ALL(School[School]),CALCULATE([Rank Total],ALL(School[School])),,ASC,DENSE)? As this returns 1 for all school rankings.

Or: Rank Total = CALCULATE([Rank Effort]+[Rank Cost/Hire]+[Rank Quality]+[Rank Hires],ALL(School[School]))? Which also returns 1 for all school rankings?

 

[gazpage]

Can you provide even a dummy version of your data?

 

[gazpage]

This help?

https://expirebox.com/download/f22acdd0a8a43548b36ecf8737af3619.html

Works in my super simple version. Not sure if you can extend that to what you need, but not sure I can help further without a better understanding of the data structure, sorry.

 

[gazpage]

Re-reading all this again, I think I may have sent you down the wrong path earlier, very sorry for that.

Even if I had your file I doubt I could work out where it is going wrong just by eyeballing. I would first identify at which stage things are going wrong, that is check if Rank Effort, Rank Cost/Hire etc return what you would expect. That would allow you to determine the exact step you are going wrong. If it is really this last step, you can still break it down further by creating individual measures for subcomponents, ie the ISBLANK part.

I don't think I have ever put a RANKX within a RANKX, which is effectively what you are doing in Rank Total. I do not know how the filters will propagate down the chain.

 

[stbrooks13]

Thank you! I think I see where you're headed. I got this error message "Error Message: MdxScript(Model) (78, 32) Calculation error in measure 'Activity'[Rank Level]: A table of multiple values was supplied where a single value was expected." I will play around with it.