Troubles with RANKX context

DAX DAX Calculations
1

Hello,

After watching EDNA’s modules on RANKX I’m still not able to solve this RANKX scenario.
The ranking in the example below should be dynamically modified by Customer in the Matrix Rows, Year in the Matrix Columns and Group , Year and Week in the slicers. The ranking is based on the Volume measure shown herebelow.

Issue:

When all weeks are selected the ranking works fine, but whenever I filter on certain weeks the ranking shows double ranks. How can I solve this?

The DAX I used:

Rank = 
RANKX( ALL(Volume[Customer]), Volume[Volume],,DESC, Dense)

Volume = SUM(Volume[Total Volume])

Screenshots of the matrix

1. Matrix when all weeks are selected, showing the right ranks

RANKX_1
RANKX_1715×737 46.8 KB

2. Matrix when certain weeks are selected, showing wrong ranks

RANKX_2
RANKX_2705×733 45.5 KB

2

Hello @Luukv93,

Thank You for posting your query onto the Forum.

It would be great for the forum members if you could upload the working of the PBIX file so that they can assist you in a better and efficient manner while you upload a query.

From what I see is that in your RANKX formula you’ve referred the Fact Table rather than referring the Dimension Table of the Customers. In my file, I tried the logic that you’ve applied and it gave me the same results that you’re facing where Ranks are being repeated until I replaced the logic with the Customer Dimension Table and then got the correct results.

Below is the screenshot provided for the reference -

RANKX Results
RANKX Results1412×800 67.7 KB

In your file, you can replace the RANKX formula with something like this -

Rank = 
RANKX( ALL( Customer[Customer] ) , Volume[Volume] , , DESC , Dense )

Note: The naming convention might change. Please replace the FactTable[Customer] with the DimensionTable[Customer].

I’m also attaching the working of the PBIX file for the reference. If this doesn’t solve the problem then please attach the working of your PBIX file so that members of our forum can have a better look at it.

Hoping you find this useful. :slightly_smiling_face:

Thanks and Warm Regards,
Harsh

RANKX - Harsh.pbix (678.2 KB)

3

Hi,

Thanks for reply. I managed to solve it myself with following DAX:

Ranking = 
IF (
    NOT ISBLANK ( Volume[Volume] ) && HASONEVALUE( Customer[Customer] ),
    CALCULATE (
        RANKX (
            CALCULATETABLE (
                ALL ( Customer[Customer] ),
                FILTER (
                    ALL ( DM_DATUM ),
                    DM_DATUM[Week]
                        >= MIN ( DM_DATUM[Week] )
                        && DM_DATUM[Week]
                            <= MAX ( DM_DATUM[Week] )
                )
            ),
            Volume[Volume],
            ,
            DESC,
            DENSE
        ),
        VALUES ( DM_DATUM[Year] ),
        VALUES ( Customer[Customer] )
    ), BLANK()
)
1 Like