Query on Ranking based on sorted values

[Anonymous]

I have created matrix based on category and sum of error count. I have tried to create Rank based on sum of error count, am getting rank as 1 for all the category. I have used below formula.

Rank =

RANKX (Sheet1,SUM(Sheet1[Err Cnt]),,DESC,Dense). 

Please guide to get a rank based on the sum of erro count by category,

Have a Nice Day!!!!!

Regards,

Ganesh N

[Anonymous]

Thank you @bhanu_gautam. it is working

[bhanu_gautam · Accepted solution]

@Anonymous , First create a calculated column 

SumErrCntByCategory =
CALCULATE(
SUM(Sheet1[Err Cnt]),
ALLEXCEPT(Sheet1, Sheet1[Category])
)

Then create a Rank Measure

Rank =
RANKX(
ALL(Sheet1[Category]),
CALCULATE(SUM(Sheet1[Err Cnt])),
,
DESC,
DENSE
)

Use Rank Measure in your visual