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?

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi v-xicai  ,

      Please could you have a look? I am not able to call [Value] in the formula.

      Thanks!

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

        Hi Anonymous ,

         

        If the [Value] is a column, you may change the formula like DAX below.

         

         

        TopN Value =
VAR _SelectedTop =
    SELECTEDVALUE ( 'TopN'[TopN] )
RETURN
    SWITCH (
        TRUE (),
        SelectedTop = 0, SUM ( [Value] ),
        RANKX ( ALLSELECTED ( Table1 ), [Value] ) <= _SelectedTop, SUM ( [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.