Dynamic double Top 10

[Anonymous]

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]

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

[v-jiascu-msft]

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] ) ) )

[Anonymous]

Hi @v-jiascu-msft

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... 

[Vvelarde · Accepted solution]

@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.

[Anonymous]

hi @Vvelarde

This is exactly what i need just would like to have it in slicer, instead of new table so i can put it horizontal slicer on top of the dashboard. 

I would say it is alternative solution and i will try it out 

thank you 

[Vvelarde]

@Anonymous

You can have a better look with a tree Map. 

[Anonymous]

hi @Vvelarde

How did you make all the groups equal size. When i try my data the panels are different sizes and i coulndt find the option to adjust it

thank you 

[Vvelarde]

@Anonymous

I use a distinct count of Product Group in Values Section