Top N using multiple slicers

[Anonymous]

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 someone show me how to?

Thanks,

[parry2k]

@Anonymous Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Anonymous]

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]

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.

[v-xicai]

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]

Hi @v-xicai ,

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

[Anonymous]

Hi @v-xicai  ,

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

Thanks!

[v-xicai]

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.