cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
doubles Regular Visitor
Regular Visitor

RANKX with summarized table in a measure

 

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)

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: RANKX with summarized table in a measure

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

 

 

 

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
2 REPLIES 2
Super User
Super User

Re: RANKX with summarized table in a measure

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

 

 

 

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
doubles Regular Visitor
Regular Visitor

Re: RANKX with summarized table in a measure

Thanks -- this was helpful.