Top n filter by products for each stock

[pstanek]

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?

[v-sihou-msft · Accepted solution]

@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)

Then you can add visual level filter.

Regards,

[pstanek]

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.