Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Ranking in Matrix Visual and Datewise

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 

Need Help
Tips and Tricks

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    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