RANKING in a Matrix with hierarchies with data from 2 tables

[yassine_mendy59]

Objective: Create a rank calculation in a matrix based on the number of customers.

So, with a hierarchy:

Region

    Store

         Department

We have table A, which contains the store ID, department, and number of customers.

We have table B, which contains the store ID, store label, and associated region label for each store ID.

The desired matrix would be:

Region (table B)

     Store (table B)

             Department (Table A)

                       SUM Number of customers (Measure)

                                   Associated rank (Measure)

The rank should therefore be calculated for each of the hierarchies based on the number of customers.

At the service level, I have no problem with the calculation, but at the other levels (store and region), I do have some issues.

At the store level, I have a calculation, but strangely, I will have the same rank for stores with 645, 636, and 623 customers, respectively. This is illogical.

At the region level, I only have 1s.

Actual measure :

Adhesions_Rank =
VAR rank_region =
RANKX(
ALL(B[region_label]),
CALCULATE([NB_CUSTOMERS]),
,
DESC,
DENSE
)

VAR rank_magasin =
RANKX(
ALL(B[entity_label]),
CALCULATE([[NB_CUSTOMERS]]),
,
DESC,
DENSE
)

VAR rank_service =
RANKX(
ALL(A[service_label]),
CALCULATE([NB_CUSTOMERS]),
,
DESC,
DENSE
)

RETURN
SWITCH(
TRUE(),
ISINSCOPE(A[service_label]), rank_service,
ISINSCOPE(B[entity_label]), rank_magasin,
ISINSCOPE(B[region_label]), rank_region
)

[Poojara_D12]

Hi @yassine_mendy59 

Can you try :

Adhesions_Rank =
SWITCH(
    TRUE(),
    
    -- Department level (rank within a store)
    ISINSCOPE(A[service_label]),
        RANKX(
            ALLSELECTED(A[service_label]),
            CALCULATE([NB_CUSTOMERS]),
            ,
            DESC,
            DENSE
        ),
    
    -- Store level (rank within a region)
    ISINSCOPE(B[entity_label]),
        RANKX(
            ALLSELECTED(B[entity_label]),
            CALCULATE([NB_CUSTOMERS]),
            ,
            DESC,
            DENSE
        ),
    
    -- Region level (rank across all regions)
    ISINSCOPE(B[region_label]),
        RANKX(
            ALLSELECTED(B[region_label]),
            CALCULATE([NB_CUSTOMERS]),
            ,
            DESC,
            DENSE
        )
)

[yassine_mendy59]

Not working either 😥

[bhanu_gautam]

@yassine_mendy59 , Try using

dax
Adhesions_Rank =
VAR rank_region =
RANKX(
ALL(B[region_label]),
CALCULATE(SUM(A[NB_CUSTOMERS])),
,
DESC,
DENSE
)

VAR rank_magasin =
RANKX(
ALL(B[store_label]),
CALCULATE(SUM(A[NB_CUSTOMERS])),
,
DESC,
DENSE
)

VAR rank_service =
RANKX(
ALL(A[department]),
CALCULATE(SUM(A[NB_CUSTOMERS])),
,
DESC,
DENSE
)

RETURN
SWITCH(
TRUE(),
ISINSCOPE(A[department]), rank_service,
ISINSCOPE(B[store_label]), rank_magasin,
ISINSCOPE(B[region_label]), rank_region
)

[Shahid12523]

Use ALLSELECTED instead of ALL so the rank respects the parent level:

Adhesions_Rank =
SWITCH(
TRUE(),

// Rank inside Department (service) for its store
ISINSCOPE(A[service_label]),
RANKX(
ALLSELECTED(A[service_label]),
[NB_CUSTOMERS],
,
DESC,
DENSE
),

// Rank inside Store for its Region
ISINSCOPE(B[entity_label]),
RANKX(
ALLSELECTED(B[entity_label]),
[NB_CUSTOMERS],
,
DESC,
DENSE
),

// Rank inside Regions (overall)
ISINSCOPE(B[region_label]),
RANKX(
ALLSELECTED(B[region_label]),
[NB_CUSTOMERS],
,
DESC,
DENSE
)
)

👉 This way:

Departments rank within their store

Stores rank within their region

Regions rank globally

[yassine_mendy59]

No changeing, always the same problem with the ranking

[Gabry]

Hey,

could you share the pbix with sample data?

[yassine_mendy59]

My pbi is linked to GCP tables so i can't give you some data.

[techies · Accepted solution]

Hi @yassine_mendy59 please try this

Rank =

VAR rank_region =

    RANKX (

        ALL ( 'Table B'[region_label] ),

        [NB_CUSTOMERS],

        ,

        DESC,

        DENSE

    )

VAR rank_store =

    RANKX (

        ALLSELECTED ( 'Table B'[store_label] ),

        [NB_CUSTOMERS],

        ,

        DESC,

        DENSE

    )

VAR rank_service =

    RANKX (

        ALLSELECTED ( 'Table A'[department] ),

        [NB_CUSTOMERS],

        ,

        DESC,

        DENSE

    )

RETURN

SWITCH (

    TRUE (),

    ISINSCOPE ( 'Table A'[department] ), rank_service,

    ISINSCOPE ( 'Table B'[store_label] ), rank_store,

    ISINSCOPE ( 'Table B'[region_label] ), rank_region

)

[v-mdharahman]

Hi @yassine_mendy59,

Thanks for reaching out to the Microsoft fabric community forum. It looks like your ranking expression is not evaluating a store/region-specific aggregation when RANKX iterates, so several store rows end up returning the same value to RANKX. Kindly go through the responses given by @techies and @bhanu_gautam and check if your issue can be resolved.

I would also take a moment to thank @techies, @Gabry, @Shahid12523 and @bhanu_gautam, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.

[v-mdharahman]

Hi @yassine_mendy59,

As we haven’t heard back from you, so just following up to our previous message. I'd like to confirm if you've successfully resolved this issue or if you need further help.

If yes, you are welcome to share your workaround so that other users can benefit as well.  And if you're still looking for guidance, feel free to give us an update, we’re here for you.

Best Regards,

Hammad.

[v-mdharahman]

Hi @yassine_mendy59,
Hope everything’s going smoothly on your end. As we haven’t heard back from you, so I wanted to check if the issue got sorted.
Still stuck? No worries just drop us a message and we can jump back in on the issue.

Best Regards,

Hammad.