Forum Discussion
Top n products every week
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
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
5 Replies
- Greg_Deckler
Community Champion
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
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?
jvangrunderbeecHelper I
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
v-cherch-msftMicrosoft Employee
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
