Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Akshay08
New Member

TOP N Slicer Isn't Working

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)




TOP N.png

1 ACCEPTED SOLUTION
v-nuoc-msft
Community Support
Community Support

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.

 

View solution in original post

4 REPLIES 4
v-nuoc-msft
Community Support
Community Support

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.

 

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
Super User
Super User

@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

Hi Amit - Thanks for sharing

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors