Ranking in Matrix Visual and Datewise

[Anonymous]

Hi Friends,

I am facing one of the peculiar situtation, i have a matrix visual like the below.

Note: In the image the formula i have used showing rank 5 for all the regions and for all the dates but i need to see actual raking here.

I am using below fields for matrix visual

I need to arrive ranking for Region (Row) for different dates (column) and also when i expand the matrix visual from Region to State Province i should see the ranking for State or Province within that region.
When i expand (Region to State or Province) i think i am seeing correct ranking values but at the Region level the ranking is showing as 5 only.

The DAX formul i have used here is below.

Sales Ranking =

IF (

ISINSCOPE(Orders[State or Province]),

RANKX(

CALCULATETABLE(

VALUES(Orders[State or Province]),

ALLSELECTED(Orders[State or Province]) ),

[Total Sales],,DESC,dense

),

IF(

ISINSCOPE(Orders[Region]),

VAR CustomerRanking = [Total Sales]

RETURN

CALCULATE(

RANKX(

VALUES(Orders[Region]),

[Total Sales],

CustomerRanking

,DESC,dense

),

ALLSELECTED()

)

)

)

Can someone help me on this here.

@amitchandak @Greg_Deckler @diex @aj1973 @BA_Pete @Jihwan_Kim 

[amitchandak]

@Anonymous , change the region if like

IF(
ISINSCOPE(Orders[Region]),
VAR CustomerRanking = [Total Sales]
RETURN
CALCULATE(
RANKX(
ALLSELECTED(Orders[Region]),
[Total Sales], ,
,DESC,dense
)
)
)

This means each region will get rank for each date

[Anonymous]

Hi Amit,

Thanks for the revert.

Your DAX is giving me correct result at Region Level.

But When i expand to State or Province it is giving me Rank 1 for all the dates.

It has to give me Raning for State or Province within that Region.

[Anonymous · Accepted solution]

HI @Anonymous,

I think you need to add if statement to check the current hierarchy level and write different rank formulas for the different level ranking:

Clever Hierarchy Handling in DAX - SQLBI

DAX – Ranking with Hierarchy Drill-Down – Azure Data Ninjago & dqops

Regards,

Xiaoxin Sheng