Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

TOP N Slicer for pivot table

Hi All, 
I need to showcase the TOP 5, TOP 10, and TOP 20  products with sales in matrix visual,

Under product, I need to show the month
So, ie
I Want to display TOP Products sales under product I need to show each month how many sales happened

I want to show the top 5 product like this using top n slicer and while i drill down to month i need to show those top 5 products how they performed in each month 

Current issue is  while applying top 5  month  at that time more poducts is coming , if we remove month from matrix visual it is showing correctly
Kindly help to achieve this dax
In matrix visual while expanding the procut the expected out put should be like this 

 




Here i am attaching the dummy data in excel





Product Year Month Sales
BD10M32January859
BD10M32February607
BD10M32March713
BD10M32April231
BD10M32May470
BD10M32June607
BD10M32July722
BD10M32August255
BD10M32September249
BD10M32October427
BD10M32November131
BD10M32December142
BD10M22January819
BD10M22February709
BD10M22March872
BD10M22April660
BD10M22May674
BD10M22June950
BD10M22July830
BD10M22August855
BD10M22September374
BD10M22October465
BD10M22November230
BD10M22December468
BD10M12January182
BD10M12February114
BD10M12March730
BD10M12April764
BD10M12May233
BD10M12June942
BD10M12July797
BD10M12August185
BD10M12September505
BD10M12October737
BD10M12November93
BD10M12December459
BD10M11January606
BD10M11February123
BD10M11March900
BD10M11April52
BD10M11May834
BD10M11June946
BD10M11July32
BD10M11August642
BD10M11September707
BD10M11October418
BD10M11November482
BD10M11December444
BD10M10January891
BD10M10February989
BD10M10March383
BD10M10April470
BD10M10May938
BD10M10June759
BD10M10July561
BD10M10August457
BD10M10September473
BD10M10October411
BD10M10November925
BD10M10December327
BD10M09January1000
BD10M09February94
BD10M09March294
BD10M09April239
BD10M09May529
BD10M09June451
BD10M09July70
BD10M09August851
BD10M09September235
BD10M09October892
BD10M09November105
BD10M09December516
BD10M08January533
BD10M08February148
BD10M08March684
BD10M08April549
BD10M08May905
BD10M08June785
BD10M08July860
BD10M08August820
BD10M08September977
BD10M08October885
BD10M08November51
BD10M08December828
BD10M07January845
BD10M07February82
BD10M07March353
BD10M07April178
BD10M07May505
BD10M07June757
BD10M07July795
BD10M07August929
BD10M07September224
BD10M07October77
BD10M07November245
BD10M07December515
BD10M06January824
BD10M06February886
BD10M06March885
BD10M06April612
BD10M06May893
BD10M06June714
BD10M06July574
BD10M06August24
BD10M06September673
BD10M06October317
BD10M06November48
BD10M06December773
BD10M08January833
BD10M08February297
BD10M08March891
BD10M08April445
BD10M08May546
BD10M08June233
BD10M08July418
BD10M08August283
BD10M08September381
BD10M08October130
BD10M08November618
BD10M08December705




Need Help
Show and Tell
Tips and Tricks

5 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    3 years ago

    Hi,

    Please check the below and the attached pbix file.

     

     

     

     

    Top 5 sales: =
VAR _list =
    CALCULATETABLE (
        TOPN ( 5, ALL ( 'Product'[Product ] ), [Sales:], DESC ),
        REMOVEFILTERS ( 'Month' )
    )
RETURN
    CALCULATE ( [Sales:], KEEPFILTERS ( 'Product'[Product ] IN _list ) )

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi Jihwan_Kim ,

      First of all, thank you for the solution,  but I am facing a challenge
      Here i have toip n slicer based on that only the pivot table should work,
      Kindly Have a look 





       

       




      • MarkLaf's avatar
        MarkLaf
        Icon for Super User rankSuper User
        3 years ago

        You can create a measure to use as a filter on the matrix.

        Create a table for your Top N options - here is DAX for 5, 10, 20:

        N Options = { 5, 10, 20 }


        Create the measure that you'll use as a filter:

        TopNProductSalesFilter = 
VAR _N = MAX( 'N Options'[Value] )
VAR _topN = 
CALCULATETABLE( 
    TOPN( _N, ALL( 'Table'[Product] ), CALCULATE( SUM('Table'[Sales] ) ) ),
    REMOVEFILTERS( 'Table' ),
    VALUES( 'Table'[Product] )
)
RETURN
IF( 
    ISFILTERED( 'N Options'[Value] ), 
    CALCULATE( INT( NOT ISEMPTY( 'Table' ) ), KEEPFILTERS( _topN ) ), 
    1 
)

         

        Now put your matrix together and add [TopNProductSalesFilter] as a filter and set it equal to 1. Put 'N Options'[Value] in a slicer:

         

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    3 years ago

    Anonymous See attached with Top N selection. Jihwan_Kim has provided a great solution, and I tweaked DAX measure a bit.

     

    Top 5 sales: = 
VAR _list =
    CALCULATETABLE (
        TOPN ( [Top N Selected], ALL ( 'Product'[Product ] ), [Sales:], DESC ),
        REMOVEFILTERS ( 'Month' )
    )
RETURN
    CALCULATE ( [Sales:], KEEPFILTERS ( _list ) )
)

     

    👉 Learn Power BI  to our YT channel - @PowerBIHowTo