Top N products sold per product category

[plaa]

Hi, 

I'm totally new with Power BI and already ran in trouble when trying to create this simple table.

I need to create a table which has top 20 products sold for each product category. For example, if I have three product categories which have 100 products each, I need to create a table which has 60 products in total grouped in 3 categories (3 categories x top 20 products).

Could someone give some pointers how to solve this? 

Br, Pekka

[v-ljerr-msft · Accepted solution]

Hi @plaa,

If I understand you correctly, you should be able to follow steps below to create the table which has top 20 products sold for each product category. 

1. Use the formula below to create a measure to calculate Total Sales.

Total Sales = SUM(Sales[Revenue])

2. Use the formula below to create a calculate column in Product table to rank product sold for each product category.

Rank =
RANKX (
    ALLEXCEPT ( Products, Products[Category] ),
    [Total Sales],
    ,
    DESC,
    DENSE
)

3. Then you should be able to use the formula below to create the table.

Table = 
SELECTCOLUMNS (
    FILTER ( Products, Products[Rank] <= 20 ),
    "Category", Products[Category],
    "Product", Products[Product],
    "Rank", Products[Rank]
)

Regards