Rankx, Ranking by Region and by Zone (multiple level of hierarchy) using Matrix style table

[123abc]

Dear Sirs & Experts,

I want a DAX measure that can calculate Ranking for Main Category then furthermore 3 sub-categories based on %.

Necessary Reference Files and expected results is attached below link (Excel and pbix):

https://drive.google.com/file/d/1XOUWo-EyXJVcT1_6LPCWgc38pn0dkUnP/view?usp=sharing

Old Dax not work:

Rank =
SWITCH (
    TRUE (),
    ISINSCOPE ( ASO[ASO Name] ),
        RANK (
            SKIP,
            ALL ( ASO[ASO Name], ASO[Branch], ASO[Zone], ASO[Region] ),
            ORDERBY ( CALCULATE ( SUM ( ASO[Sales Ach] ) ), DESC )
        ),
    ISINSCOPE ( ASO[Branch] ),
        RANK (
            SKIP,
            ALL ( ASO[Branch], ASO[Zone], ASO[Region] ),
            ORDERBY ( CALCULATE ( SUM ( ASO[Sales Ach] ) ), DESC )
        ),
    ISINSCOPE ( ASO[Zone] ),
        RANK (
            SKIP,
            ALL ( ASO[Zone], ASO[Region] ),
            ORDERBY ( CALCULATE ( SUM ( ASO[Sales Ach] ) ), DESC )
        ),
    ISINSCOPE ( ASO[Region] ),
        RANK (
            SKIP,
            ALL ( ASO[Region] ),
            ORDERBY ( CALCULATE ( SUM ( ASO[Sales Ach] ) ), DESC )
        )
)

Working Design On Power BI Dashboard and Expected Results showing in Excel Sheet:

@  Jihwan_Kim @Ashish_Mathur  @amitchandak

@FreemanZ

[123abc · Accepted solution]

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) We can create a measure.

Rank = 
SWITCH (
    TRUE (),
    ISINSCOPE ( 'unique keys'[ASO Name] ),
        RANK (
            SKIP,
            ALL ( 'unique keys'[ASO Name], 'unique keys'[Branch], 'unique keys'[Zone], 'unique keys'[Region] ),
            ORDERBY ( CALCULATE ( SUM ('Sales data'[Sales Ach] ) ), DESC )
        ),
    ISINSCOPE ( 'unique keys'[Branch] ),
        RANK (
            SKIP,
            ALL ( 'unique keys'[Branch], 'unique keys'[Zone], 'unique keys'[Region] ),
            ORDERBY ( CALCULATE ( SUM ('Sales data'[Sales Ach] ) ), DESC )
        ),
    ISINSCOPE ( 'unique keys'[Zone] ),
        RANK (
            SKIP,
            ALL ( 'unique keys'[Zone], 'unique keys'[Region] ),
            ORDERBY ( CALCULATE ( SUM ( 'Sales data'[Sales Ach] ) ), DESC )
        ),
    ISINSCOPE ( 'unique keys'[Region] ),
        RANK (
            SKIP,
            ALL ( 'unique keys'[Region] ),
            ORDERBY ( CALCULATE ( SUM ( 'Sales data'[Sales Ach] ) ), DESC )
        )
)

(3) Then the result is as follows.

Best Regards,

Neeko Tang