Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

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 un...
Need Help
  • Vvelarde's avatar
    Vvelarde
    9 years ago

    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's avatar
Anonymous
Not applicable
9 years ago

Here is a test sample 

 

 

RegionProduct groupproduct sale 2017sale 2016
Germanysporta1278
Germanysportb1385
Germanysportc1141
Germanysportd111874
Germanysporte13471
Germanysportf4278
Germanycasualg 5445
Germanycasualh6487
Germanycasuali7842
Francesandalsj7554
Francesandalsk5564
Francesandalsl 178
Francesandalsm3375
Francesporta3555
Francesportb561
Francecasualg 6733
Francecasualh6535
Francecasuali5333
UK sportd44134
UK sporte66542
UK sportf6554
UK sandalsl 77364
UK sandalsm7878
UK sporta2475
UK sportb54255
UK casualg 7771
UK casualh3233
UK outdoorw4235
UK outdooru77866

 

 

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's avatar
    v-jiascu-msft
    Microsoft Employee
    9 years ago

    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's avatar
      Anonymous
      Not applicable
      9 years ago

      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's avatar
        Vvelarde
        Community Champion
        9 years ago

        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.