Help Needed: Understanding Differences in RANKX Measures in Power BI

[Chateauunoirr]

Hello everyone,

I am currently working on Power BI and have created two measures to rank a table of series. Here are the measures I used:

1. Rank1 = RANKX(ALL(Series), SUM(Series[Viewers (m)]))
2. Rank2 = RANKX(ALL(Series), CALCULATE(SUM(Series[Viewers (m)])))

I don't understand why the second measure (Rank2) correctly ranks my dataset, while the first measure (Rank1) only returns Rank = 1 for all series.

Could someone explain the difference between these two measures and why Rank2 works while Rank1 does not?

Thank you in advance for your help!

[bhanu_gautam · Accepted solution]

@Chateauunoirr , For 1 st one

SUM(Series[Viewers (m)]) is evaluated in the current row context of the Series table.

Since RANKX iterates over the table specified in the first argument (ALL(Series)), it evaluates SUM(Series[Viewers (m)]) in the context of each row. However, without a CALCULATE function, the SUM function does not properly aggregate the values across the entire table, leading to incorrect results where each row context might just return the value of the current row, resulting in all ranks being 1.

In second CALCULATE(SUM(Series[Viewers (m)])) changes the context in which the SUM function is evaluated. CALCULATE forces the SUM to be evaluated in the context of the entire table (due to ALL(Series)), thus correctly aggregating the Viewers (m) values across all rows in the Series table. This allows RANKX to correctly rank the series based on the total viewers.

[Chateauunoirr]

Hello @bhanu_gautam Thank you very much for your answer, I have two questions that emerge following your answer:
- If I remove the ALL argument to just have a RANKX on the 'Series' and not ALL('Series') would it have worked for the second measure?
- Should I always use the calculate function when I evaluate an expression inside another?

[bhanu_gautam]

@Chateauunoirr If you remove the ALL argument and just use RANKX on the Series table, the second measure would still work correctly because CALCULATE ensures that the SUM function is evaluated in the correct context. However, the ranking might be affected by any existing filters on the Series table.

It is generally a good practice to use the CALCULATE function when you need to change the context in which an expression is evaluated, especially when dealing with aggregations inside iterators like RANKX.

[Chateauunoirr]

Understood thank you for your answers, it really helped me