Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Filter Top N customers using slicer

Hi everyone,   I have a table containes customerID and sales amount. I want use the sales amount and growth rate to create a scatter chart.  I hope that the users can select top 10, 20, 50 customer...
Need Help
Zubair_Muhammad's avatar
Zubair_Muhammad
Icon for Community Champion rankCommunity Champion
8 years ago

Hi Anonymous

 

You can do this with the help of a small PARAMETER TABLE.

 

For example, create a small PARAMETER Table like

 

CategoryValue
Top 55
Top 1010
Top 1515
Top 2020

 

Then  add a slicer of "Category" above

 

Now Add a MEASURE in your DataTable which will determine if the Customers fall in the CATEGORY selected in a slicer

 

Is Top X selected? =
IF (
    RANKX (
        ALL ( 'DataTable' ),
        CALCULATE ( SUM ( 'DataTable'[Sales Amount] ) ),
        ,
        DESC,
        DENSE
    )
        <= SELECTEDVALUE ( ParameterTable[Value] ),
    "Yes"
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Hi Zubair_Muhammad,

       

      Thank you so much for your reply and I tried to do what you said. But I got some problem and could you please help me with it?

       

      This is what I have right now. It seems that if I choose Top 5, more than 5 customers are returned.

       

      Thank you!

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      oh I know my mistake. Thank you very much!

      • erichard00's avatar
        erichard00
        Frequent Visitor
        8 years ago

        curious what your mistake might be.. I can't get this to work at all??? the download file does not match above.. and the instructions above do not say to add a col called "is top x selected" or how it works... and ... in the downloaded file, there are 2 measures..(one called "other" and the other called "Top x" very confusing with inconsistent , incomplete instructions....