Ranking with multi Columns in Matrix Visual

[tracytran91]

Hi friends, 

I would like to rank our clients based on their contribution in each region.  Therefore, Each Client should have their own rank in each region. Here is my DAX: 

The result didnt correct as I expect. 

Any help is highly appreciated.

[tracytran91]

@v-alq-msft OMG! you saved my day!! Thank a lot buddy!

[v-alq-msft · Accepted solution]

Hi, @tracytran91 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

You may create a measure as below.

Client Rank = 
IF(
    ISINSCOPE('Table'[Client Name]),
    var c = SUM('Table'[%Count])
    var clientrank = 
    RANKX(
        ALLEXCEPT('Table','Table'[Location Definition]),
        CALCULATE(SUM('Table'[%Count])),
        ,,Dense
    )
    var result = 
    IF(
        NOT(ISBLANK(c)),
        clientrank
    )
    return
    result
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@tracytran91 , not very clear based on screen shot. refer for category rank in the link

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://www.youtube.com/watch?v=DZb_6j6WuZ0