Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
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
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
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
So, a variation on that RANKX measure and you have 2 slicers for week and product? Is that how you have this setup?