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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
pstanek
Post Patron
Post Patron

Top n filter by products for each stock

Hi ,

do you have any experience with  TOP N filter.

 

I have this problem .  I have three columns within database -- Stock, Product, Profit.

 

I need to create visual - TOP 20  products by profit, for each stock.

So I don´t want to have-  TOP 20  products by profit ror all stocks.

 

So I mean  Top 20 products for stock 1, top 20 products for stock 2, ..................................................................

 

Should I use matrix or something else? Will TOP N filter work for this problem?

1 ACCEPTED SOLUTION
v-sihou-msft
Employee
Employee

@pstanek

 

In this scenario, you need to create a RANK column/measure for filtering. The formula can be like:

 

= RANKX(ALL(Table2[Product]),CALCULATE(SUM(Table2[Sales]),ALLEXCEPT(Table2,Table2[Product],Table2[Stock])),,DESC,Dense)

88.PNG

 

 

Then you can add visual level filter.

 

888.PNG

 

Regards,

View solution in original post

2 REPLIES 2
v-sihou-msft
Employee
Employee

@pstanek

 

In this scenario, you need to create a RANK column/measure for filtering. The formula can be like:

 

= RANKX(ALL(Table2[Product]),CALCULATE(SUM(Table2[Sales]),ALLEXCEPT(Table2,Table2[Product],Table2[Stock])),,DESC,Dense)

88.PNG

 

 

Then you can add visual level filter.

 

888.PNG

 

Regards,

 

 

 

 

 

Poradi_pdk = RANKX(ALL(movement[pdk]);CALCULATE(SUM(movement[pcena_bd_celkem]);ALLEXCEPT(movement;movement[pdk];category_management[nadkategorie])))

 

 

Can you see where I made a mistake ?

I have stock called nadkategorie and they are in another table?

 

Poradi_pdk lost some products somewhere. When I use filter <=10 , many of product aren´t find by this filter.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.