Forum Discussion
Dynamic double Top 10
Hi
I need help with this scenario
I have sales data which has product name, product group, region and sold units for year 2016 and 17
I want to display top 10 product groups based on the units sold in 2017 and within the groups i would like to show top 10 bestselling products also based on the 2017 units sold
i would like to use two slicers one is for the top 10 product groups and one for the region
Then i will show the top 10 products in table
The top 10 products within the product group i will do with the top N filter. But I am struggling to create measure or column which will change the top 10 product groups every time i change the region.
I created a table where I ranked the product groups by units sold in 2017
Then i created another table which only displays the top 10 product groups and this i put in the slicer. However when I select different region the top 10 product groups do not change
Can someone help
Thank you
Anonymous
Hi, i'm not sure if this what you need.
Use a Slicer to Regions
Use the New Table Visual to show the Top 10 Product Group
Use a Table to show the top 10 Products of the selected Group.
8 Replies
Here is a test sample
Region Product group product sale 2017 sale 2016 Germany sport a 12 78 Germany sport b 13 85 Germany sport c 11 41 Germany sport d 111 874 Germany sport e 134 71 Germany sport f 42 78 Germany casual g 54 45 Germany casual h 64 87 Germany casual i 78 42 France sandals j 75 54 France sandals k 55 64 France sandals l 1 78 France sandals m 33 75 France sport a 35 55 France sport b 56 1 France casual g 67 33 France casual h 65 35 France casual i 53 33 UK sport d 44 134 UK sport e 665 42 UK sport f 65 54 UK sandals l 773 64 UK sandals m 78 78 UK sport a 24 75 UK sport b 542 55 UK casual g 777 1 UK casual h 32 33 UK outdoor w 42 35 UK outdoor u 778 66 and I would like to be able select UK in slicer - that will automatically pick the top N product groups for that region which will put display in slacer.
In the product group slicer i will select one fo the product groups and it will show me the top N productc per that category
I hope it makes sence
I was trying to TOP N meassure
top 10 produc group = TOPN(10,SUMMARIZE('Test Table'
,'Test Table '[Product Group]
,"Sale 2017", SUM('Test Table'[Sale 2017])
)but it didnt work the way i expected
Hi, Anonymous
A measure can’t be used in a slicer. And it seems that the contents of a slicer can’t be changed dynamically. Maybe you can try to create two measures and a slicer. Then you can use filter to get the top N. You can have a try.
RankByProductGroup =
CALCULATE (
RANKX ( ALL ( Table1[Product group] ), CALCULATE ( SUM ( Table1[sale 2017] ) ) ),
ALLEXCEPT ( Table1, Table1[Region], Table1[Product group] )
)RankByProduct =
RANKX ( ALL ( Table1[product] ), CALCULATE ( SUM ( Table1[sale 2017] ) ) )Thank you for your reply
I think i was not clear enough on what I am looking for. I dont need to display the ranking. I just want to uste the ranking as a filter/slicer to display the sale per product
so the table should look like this
product units sold
a 12
b 13
when the region slicer would be germany a product gruou slicer will be sport. As my data set is much larger than this test data i will only show the top 10 products within the top 10 product groups for all regions...
