TOP N Slicer Isn't Working

[Akshay08]

Hi Team,

I am looking for a solution on below scenario.
We would need Top N slicer on canvas to let the user filter Matrix visual according to their need.
Requirement is to filter Region by Top N value and it should show all customer under that region (Matrix Visual).

I am able to achieve this by using What if parameter and rank(), and its working fine for Region (PBIX is attached).
Issue is, whenever we pull Customer Name in the visual, it stops filtering region and Sum [Sales] changes. For example Total sale for west region is 504,604 and when we pull customer it become 471,036.

I am having hard time to understand this behavior. Ideal situation is to have 504,604 as sale and once we expand the region it should show all customer under that region.

I think I am missing somewhere. I would appreciate any help on this. 

Measure used.

_Rank Based on sales = RANKX( ALLSELECTED(Orders[Region]),[_Sales],,DESC,Dense)

_Sales = SUM(Orders[Sales])

_Top N filter =IF([_Rank Based on sales]<='TopN'[TopN Value],1,0)

[Anonymous · Accepted solution]

Hi @Akshay08

@amitchandak Thank you very much for sharing!

Based on the image you uploaded, it seems that you are using a matrix visual with Region and Customer Name in the rows, and _Sales and _Rank Based on sales in the values. You also have a slicer for TopN Value.

One possible reason why your _Sales value changes when you pull Customer Name in the visual is that you are using ALLSELECTED in your _Rank Based on sales measure.

This means that the ranking is based on the filter context of the visual, which includes both Region and Customer Name. Therefore, when you expand a region, you are actually seeing the rank and sales of each customer within that region, not the rank and sales of the region itself.

To fix this, you can change your _Rank Based on sales measure to use ALL instead of ALLSELECTED. This will ignore the filter context of the visual and rank the regions based on the total sales, regardless of the customer name. Here is the modified measure:

_Rank Based on sales = RANKX( ALL(Orders[Region]),[_Sales],,DESC,Dense)

You can view the link below for more details:

ALLSELECTED function (DAX) - DAX | Microsoft Learn

ALL function (DAX) - DAX | Microsoft Learn

Regards,

Nono Chen

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

[Akshay08]

Hi Nono,

Thanks for your input. It worked for me with some changes.

CALCULATE

(

    RANKX (ALL('Orders'[Region]),[_Sales],,DESC,Dense),

    REMOVEFILTERS(Orders[Customer Name],Orders[Category])

)

[amitchandak]

@Akshay08 , Use TOPN funtion

M1= calculate([_Sales], keepfilters(topn('TopN'[TopN Value], ALLSELECTED(Orders[Region]), [_Sales], desc) )

Dynamic TOPN using TOPN/Window and Numeric parameter: https://youtu.be/vm2mdEioQPQ

[Akshay08]

Hi Amit - Thanks for sharing