Top n products every week

[Nouha]

Hello ,

I have a dataset that looks like this

Week      Product         Sales

1                 A                   10

1                 B                    8 

1                 C                    9

1                 X                     2

...

2                 B                     1

2                 C                     50

2                 D                    6

...

3                A                     5

3                D                     3

3                 X                     1

3                B

3                 C

I want to be able to calculate a column that gives 1 if a product is in the top 10 sales of that week and 0 if its not in the top on that specific week

or any other creative way to indentify the Top 10 of every week in order to calculate an average of sales of the top 10 products of each week

Thank you a lot

Nouha 

[v-cherch-msft · Accepted solution]

Hi @Nouha

You may refer to below measure which is the average of Top3 product sales' average value.

Measure 2 =
AVERAGEX (
    Table,
    CALCULATE (
        SUM ( Table[Sales] ),
        TOPN (
            3,
            ALLEXCEPT ( Table, Table[Week] ),
            CALCULATE ( SUM ( Table[Sales] ) ), DESC
        )
    )
        / 3
)

Regards,

Cherie

[jvangrunderbeec]

Sounds like you could use the TOPN function to create a new table. It is going to be a lot of work, but it could be done.

https://msdn.microsoft.com/en-us/query-bi/dax/topn-function-dax

[Greg_Deckler]

Sounds like you want a matrix visualization with Week and Product in the rows area and a measure based upon RANKX as a value.

https://msdn.microsoft.com/en-us/query-bi/dax/rankx-function-dax

[Nouha]

Hello, 

unfortunatly I need to display as a card visualisation, i can do it in a table of a matrix but i need to calculate it and to be displayed in a card 

[Greg_Deckler]

So, a variation on that RANKX measure and you have 2 slicers for week and product? Is that how you have this setup?