topic Re: Total Ranking when filtered in DAX Commands and Tips

Total Ranking when filtered

bfuller62 — Wed, 03 Jul 2024 19:38:13 GMT

Hello,

I'm struggling with a RANKX measure showing the correct rank when filtering.

I have a matrix that is filterd for a specific customer with product type, $, % Chg, and their $ ranking against ALL customers in the selected market for the selected report period.

The Market and Report Period slicers are from reference tables with 1:Many relations to the sales table

The problem I'm having is the rank for the yellow product type is wrong... they are actually 1st overall in that product type.

However if I put the customer name in the matrix and expand the type subtotal, it shows the correct rank (1st) at the customer level, but still 2nd for the product type subtotal

Current DAX Measure:

M_Rank_Category = IF(HASONEVALUE('TABLE'[CUSTOMER]), RANKX(ALL('TABLE'[CUSTOMER]),[M_$CY]))
where M_$CY = SUM(TABLE'[CY Sales $])

Re: Total Ranking when filtered

Irwan — Wed, 03 Jul 2024 21:19:07 GMT

Hello bfuller62

from information you gave, looks like the issue is from using ALL function in your RANKX.

RANKX will calculate whole data (with or without getting sliced/filtered) since ALL will count everything in table inside ALL function.

If you want to filtered the result, try using customer table directly without ALL and see if it solves your problem.

Hope this will help you.

Thank you.

Re: Total Ranking when filtered

Anonymous — Tue, 23 Jul 2024 08:04:45 GMT

Hi bfuller62

Is your problem solved, if so, please mark the corresponding reply as a solution, which will help users with the same problem to better solve their problem.

Best Regards,

Jayleny

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