topic Re: RANKX - issue when applying slicer (incorect ranking) in DAX Commands and Tips

RANKX - issue when applying slicer (incorect ranking)

gumis_rulez — Mon, 12 Aug 2024 17:44:28 GMT

I am making a simple product category ranking by product category.

When I use rankx function without slicer, ranking is as follows:

When I apply slicer filter f.e. on Packaged Chocalate, the ranking changes from 9 to 10:

Some rank positions after applying filter remain the same, some not.

The ranking function looks as follows:

Rank Test =

IF(

HASONEVALUE(

'Product Lookup (Updated)'[product_category]

RANKX(

ALL(

'Product Lookup (Updated)'[product_category]

[Total Sales]

BLANK()

)

The file in the attachment.https://drive.google.com/file/d/1o7ZSpWrt-3yNlhVM6n5ZV26GnpfO5sx_/view?usp=sharing

The exact problem above - on page 4

Re: RANKX - issue when applying slicer (incorect ranking)

gmsamborn — Mon, 12 Aug 2024 18:23:10 GMT

Hi gumis_rulez

Have you tried changing ALL to ALLSELECTED?

Rank Test = IF( HASONEVALUE( 'Product Lookup (Updated)'[product_category] ), RANKX( ALLSELECTED( 'Product Lookup (Updated)'[product_category] ), [Total Sales] ), BLANK() )

Let me know if you have any questions.

Re: RANKX - issue when applying slicer (incorect ranking)

gumis_rulez — Mon, 12 Aug 2024 18:40:21 GMT

When using ALLSELECTED if you select only one category, this category will have rank 1.

The goal is when you have selected packaged chocolate the rank should be calculated based on total sales of all categories and have 9 in this example.

Re: RANKX - issue when applying slicer (incorect ranking)

ThxAlot — Mon, 12 Aug 2024 22:56:46 GMT

Nothing but (inevitable) floating-point inaccuracy ever since the creation of modern computer (binary notation)

The solution is very straightforward,

Total Sales = INT( SUMX( 'Sales by Store', 'Sales by Store'[quantity_sold] * 'Sales by Store'[unit_price] ) )