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

    • venkatasuresh_g's avatar
      venkatasuresh_g
      Icon for Helper IV rankHelper IV
      6 years ago

      Hi ,

       

       I am working on same issue , I ran in to the same problem as you . Can you please let us know how  you were able to fix it. It will help people who come to this thread with same question 

       

      Thanks

      Suresh

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Is Top X selected? =
        IF (
            RANKX (
                ALL ( 'DataTable'[Customer ID] ),
                CALCULATE ( SUM ( 'DataTable'[Sales Amount] ) ),
                ,
                DESC,
                DENSE
            )
                <= SELECTEDVALUE ( ParameterTable[Value] ),
            "Yes"
        )

         

        You must specify what to group them by, in this case you are ranking them by Customer.