RANKX with summarized table in a measure

[doubles]

I am trying to use RANKX to create a measure to rank percent changes in a summarized table with the below formula -- I am getting an error that the value for 'PercentChange' cannot be determined. Can you not rank over a summarized table in a measure?

RankPercentChange =
VAR MaxDate = MAX(Dates[Date])

RETURN

RANKX(SUMMARIZE (
Data,
[State],
"AvgExpense", CALCULATE(AVERAGE ( Data[Expense] )),
"AvgExpensePrevYr", CALCULATE (AVERAGE ( ResolutionData[SettlementAmount] ),Dates[Date] < MaxDate),

"PercentChange", DIVIDE(AvgExpense - AvgExpensePrevYr, AvgExpensePrevYr,0)
)
, [PercentChange],,DESC)

[TomMartens · Accepted solution]

Hey,

I'm not sure but this "pattern" works for me, this measure returns a rank from a "summarized" table, but actually I'm using GROUPBY()

var atable = a DAX statement that returns a table
return
GROUPBY(			
	ADDCOLUMNS(
	GROUPBY(
			atable
			,'dim Category'[Category]
			)
	,"the Rank",[ms Rank]
	)
	,"Value", SUMX(CURRENTGROUP(), [the Rank])
)

This is the simple RANK measure that is used inside the statement above

ms Rank = 
RANKX(
    ALLSELECTED('dim category')
    ,[one Measure]
) 

Hopefully this gives you some idea how to adjust your Measure

Regards,
Tom

[doubles]

Thanks -- this was helpful.