Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Top N using multiple slicers

Hi,   I need to rank performance of products using multiple slicers. For example, selecting top N products selling well in October 2020. The slicers here are Date and Top N.   Please could someon...
Need Help
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Thanks for the remind parry2k . I did a few hours of research for this problem and couldn't find the answer.

    Also, the question seems so simple to describe without using the underlying data.

     

    But here is my data, if you will. 

    And the questions are here again (1) how to create a Top N slicer for these sub-product category and (2) the top slicer needs to reflect the date/time of analysis. For example, top 5 products in Feb would be different than in Jan.

     

    • v-xicai's avatar
      v-xicai
      Community Support
      6 years ago

      Hi Anonymous ,

       

      You may create new calculated table, rename the result column with "TopN", use the column  'TopN'[TopN] as the source of slicer.

       

      TopN= GENERATESERIES(0, 10, 1)

       

      Then you may create measure like DAX below.

       

      TopN Value =

VAR _SelectedTop =

    SELECTEDVALUE ( 'TopN'[TopN] )

RETURN

    SWITCH (

        TRUE (),

        SelectedTop = 0, [Value],

        RANKX ( ALLSELECTED ( Table1 ), [Value] ) <= _SelectedTop, [Value]

    )

       

      Best Regards,

      Amy 

       

      Community Support Team _ Amy

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi v-xicai ,

         

        I couldn't call [Value] after selectedTop= 0. Is it because [Value] should be a measure rather than a column?

    • v-xicai's avatar
      v-xicai
      Community Support
      6 years ago

      Hello @sngo ,

      You can create a new calculated table, rename the result column with "TopN", use the 'TopN'[TopN] column as the segmentation source.

      TopN= GENERATESERIES(0, 10, 1)

      You can then create a measure as DAX below.

      TopN Value =

VAR _SelectedTop =

SELECTEDVALUE ( 'TopN'[TopN] )

RETURN

SWITCH (

TRUE (),

SelectedTop = 0, [Value],

RANKX ( ALLSELECTED ( Table1 ), [Value] ) <= _SelectedTop, [Value]

)

      Best regards

      Amy

      Community Support Team _ Amy

      If this post helps,then please consider accepting it as the solution to help other members find it more quickly.