Develop a Ranking based On filtered Data (Static & Dynamic)

[SinaA]

Hi all,

I created a measure that will rank universities based on the total score they obtained from multiple projects. This ranking is dynamic based on the applied filters such as Year and Journal.
Suppose a user filters for a specific university using the slicer. In that case, it will impact the rank as the rank is calculated based on the current filter context, which includes the university selection. However, I want to show the rank of a selected university against all universities (regardless of any other applied filters). For example, if UNI X ranks based on selected years and journals are 32, I want this rank to show up after users filter for UNI X and see the number 32. My issue is once I filter for the particular university to see its rank, the rank will be changed to another number which is not accurate. Here is my DAX code:

NewRank = RANKX ( ALL('SinaA'[University]), CALCULATE (SUM('SinaA'[Score]), ALLEXCEPT(SinaA, SinaA[University],SinaA[Journal], SinaA[Year])), , DESC, Dense )

Sample Data:

https://wichitaedu-my.sharepoint.com/:x:/g/personal/z829w854_wichita_edu/EbUX4mTGpcFCpsm-ROXjuRkBhgr...

Thanks!

[v-yiruan-msft · Accepted solution]

Hi @SinaA ,

Please update the formula of measure [NewRank] as below and check if it can return the expected result...

NewRank =
RANKX (
    ALLSELECTED ( 'SinaA'[University] ),
    CALCULATE (
        SUM ( 'SinaA'[Score] ),
        ALLEXCEPT ( SinaA, SinaA[University], SinaA[Journal], SinaA[Year] )
    ),
    ,
    DESC,
    DENSE
)

In addition, you can refer the following links to try to solve the problem.

How to use RANKX in DAX (Part 1 of 3 - Calculated Columns) - RADACAD

powerbi - RANKX does not give the correct result - Stack Overflow

Solved: Issues with RANKX - Microsoft Fabric Community

If the above one can't help you, could you please provide more special examples to explain your expected result base on your sample data file?It would be helpful to find out the solution. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards