Forum Discussion

tracytran91's avatar
tracytran91
Helper III
5 years ago
Solved

Ranking with multi Columns in Matrix Visual

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 ...
Need Help
  • v-alq-msft's avatar
    v-alq-msft
    5 years ago

    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.

v-alq-msft's avatar
v-alq-msft
Community Support
5 years ago

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.