Ranking based on category and sub category

Anonymous · ‎02-11-2020

Hi All,

I am having a table with dealer, their sales, their state. I want to calculate the rank according to state and national.

My expected output is:-

State Dealer Brands Amnt National Rank State Rank
NSW A B1 6 1 1
NSW A B2 4 3 2
NSW B B2 15 2 1
NSW B B3 15 1 1
WA C B3 10 2 1
WA C B4 25 1 1
WA C B1 5 2 1

National Rank:-Suppose we have selected B1 and 2 dealers have sold it so they will be ranked accordingly.

It should be filterd dynamically with the year and month selection.

Please if anyone can help.

Thanks.

WA D B2 20 1 1

Anonymous · ‎02-11-2020

hi @Anonymous ,

I'm not sure if i have understood you correctly, but here I'm creating Two MEASURES to calculate rank by NATIONAL and STATE.

Here is the DAX formula to make Ranking of National.

RankColorGroup =
IF (
    'Table'[NATIONAL] = "A",
    RANKX ( FILTER ( 'Table', 'Table'[NATIONAL] = "A" ), 'Table'[SALES],, ASC ),
    IF (
        'Table'[NATIONAL] = "B",
        RANKX ( FILTER ('Table', 'Table'[NATIONAL] = "B" ), 'Table'[SALES],, ASC ),
        IF (
            'Table'[NATIONAL] = "C",
            RANKX ( FILTER ( 'Table', 'Table'[NATIONAL] = "C" ), 'Table'[SALES],, ASC )
        )
    )
)

Use the similar formula to rank state.

RankColorGroup =
IF (
    'Table'[STATE] = "VIC",
    RANKX ( FILTER ( 'Table', 'Table'[STATE] = "VIC" ), 'Table'[SALES],, ASC ),
    IF (
        'Table'[NATIONAL] = "QLD",
        RANKX ( FILTER ('Table', 'Table'[STATE] = "QLD" ), 'Table'[SALES],, ASC ),
        IF (
            'Table'[NATIONAL] = "TAS",
            RANKX ( FILTER ( 'Table', 'Table'[STATE] = "TAS" ), 'Table'[SALES],, ASC )
        )
    )
)

NOTE : make sure to change Date column to date type otherwise you will end up with the same issue you are facing.

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.

Regards,

Gaurav Raj Singh

LinkedIN : https://www.linkedin.com/in/gauravrajsingh/