cancel
Showing results for
Did you mean:
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

## 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(
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]
) ```

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

## 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(
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]
) ```

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!
Regular Visitor