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 it is calculated based on the current filter context, including 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 )
Here is the ranking link:
The following helps you to better understand the issue:
Use the link above and filter for the first 2 journals (European Journal of Marketing and industrial marketing management), then select the years 2018 and 2019
You will see the ranking table will show up. According to the table, Cardiff University's rank is 6. But when I filter for Cardiff University, using the university slicer, the rank will change to 4, which is not accurate, I want to see 6
Thanks
I tried ALLSELECTED instead of ALL and it returned RANK 1 for all universities.