Rank Institutions by YoY change for each year measure

[zanderson45]

Hello, I have a data set that has multiple years and multiple institutions in it. I created a YoY measure by first creating a measure for LY and then calculated the YoY with 

HERD YoY (%) =

DIVIDE(Sum(DimRankings[Expenditures]) - [Awarded Amount LY Column], [Awarded Amount LY Column], 0).

However I need to rank each institution by their YoY for each year. I have tried: 

Rank YoY =
RANKX(ALLEXCEPT(DimRankings,DimRankings[InstitutionName],DimRankings[YearDate]),DIVIDE(SUM(DimRankings[Expenditures]) - [Awarded Amount LY], [Awarded Amount LY], 0),
DESC,
Dense

This only shows 1 for all of the institutions rank and not individual ranks. Is there a way to rank each institution by YoY change for each year?

Thanks

[v-linhuizh-msft]

Thanks for the reply from rohit1991.

Hi @zanderson45 ,

Please try the following formula:

Rank YoY = 
RANKX(ALL('DimRankings'[YearDate]),[HERD YoY (%)],,
DESC,
Dense)

Result:

Best Regards,
Zhu

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

[zanderson45]

Thank you for the reply. I am looking to rank each institution against one another by year. In the above example that would mean ranking institution A, B,  and C for 2021, 2022, 2023, and 2024. So for 2022 the rank would be #1 C, #2 A, and #3 B and so on.

[v-linhuizh-msft · Accepted solution]

Hi @zanderson45 ,

The modified formula is as follows:

Rank YoY = 
RANKX(ALL('DimRankings'[InstitutionName]),[HERD YoY (%)],,
DESC,
Dense)

Best Regards,
Zhu

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

[zanderson45]

Thank you for your response. I tired that and I received the error "Too many arguments were passed to the EARLIER function. The maximum argument count for the function is 2." with the EARLIER(DimRankings[InstitutionName]) underlined. Looking up the error it says that EARLIER(DimRankings[InstitutionName]) doesn't need to pass twice.

[rohit1991]

you can try this :
Rank YoY =
RANKX(
FILTER(
ALLEXCEPT(DimRankings, DimRankings[YearDate]),
DimRankings[InstitutionName] = EARLIER(DimRankings[InstitutionName])
),
[HERD YoY (%)],
, DESC,
DENSE
)