Using a measure within a measure

[paulpassot78]

Hi,

I have a simple table with two columns : City Name, and Value

I'm trying to create a measure that ranks cites by values.

I created a measure = RANKX(ALL(Cities), SUM(Values)). 

It returns 1 everywhere whenever I display a table of cities with this measures.

However, if I create another measure = SUM(Values) and then reference that measure within my first measure:

RANKX(ALL(Cities), [Sum Value Measure])

it works fine.

Why would these two methods display different results?!

Thanks!

Paul

[tmack99]

It's likely that the issue is due to the way that the SUM(Values) measure is being calculated within the RANKX function. In the first case, the SUM(Values) measure is being calculated on each individual row of the table, which means that every row will have the same value for the SUM(Values) measure. This will cause the RANKX function to always return 1, since all of the values being ranked are the same.

In the second case, where you are using a separate SUM(Values) measure, that measure is likely being calculated at a higher level of the table, such as the entire table or a specific grouping of rows. This means that the SUM(Values) measure will have different values for each row, which will allow the RANKX function to properly rank the values.

One way to fix the issue in the first case would be to specify a specific filter or grouping within the RANKX function, like this: 

RANKX(Cities, SUM(Values),, ASC)

This will cause the RANKX function to calculate the SUM(Values) measure at the level of the Cities table or group, which should give the expected results.

[FreemanZ · Accepted solution]

hi @paulpassot78 

Because your second expression reference a measure and measures have a hidden CALCULATE arround. If you wrap a CALCULATE in your first expression, it shall also work.

Why CALCULATE is needed here?
Context transition. RANKX is an iterator, the first argument provides a row context for the second argument. A CALCULATE is needed for context transition (converting a row context to filter context), so we get different value for different city.

RANKX might be more complicated as it seems. Check this for further info:
https://www.sqlbi.com/articles/use-of-rankx-in-power-bi-measures/