Top N combine with visual filter

[Anonymous]

I'd like to have a chart shows top 10 product growth rate (by sold quantity) for products having monthly sold quantity over certain number. It seems like it's picking the top 10 first and then filtering by the quantity criteria, so I am getting incorrect outcome. I thought it should apply all other criteria first and then do the top N, but it's not. Any way to achieve what I want?

Thanks,

Wenchi

[v-yulgu-msft]

Hi @Anonymous,

Not sure how you generate product growth rate and what is the quantity criteria.

In my test, I created a calculated column as flag to determine whether current product have monthly sold quantity over certain number (in my test, quantity for each month shoud over 100), if yes, then 1, otherwise 0. Also, there is an extra column which returns product growth rate.

Then, based on above data, I created a Rank column like below:

Rank =
IF (
    'Table1'[quantity criteria] = 0,
    BLANK (),
    RANKX (
        FILTER ( 'Table1', 'Table1'[quantity criteria] = 1 ),
        'Table1'[Growth rate],
        ,
        DESC,
        DENSE
    )
)

Based on that Rank value, I added a new table using TopN function.

TopN =
TOPN ( 2, FILTER ( 'Table2', 'Table2'[Rank] <> BLANK () ), 'Table2'[Rank], ASC )

I have uploaded my .pbix file for your reference.

Best regards,
Yuliana Gu

[Anonymous]

I created a calculated measure for growth rate instead of a adding a calcuated column. This is mainly because the end users might filter by different time period or product category. I just thought the TOP N should be the last filter to apply to the visual if there are miltiple filters existed for the same visual. Just wondering if there is way to specify the filter sequence to get the correct outcome.

wenchi

[v-yulgu-msft]

Hi @Anonymous,

Please provide some sample data, I need to know your table structure and report design. Also, please tell me how you generate the measure for product growth rate and what is the quantity criteria.

Best regards,
Yuliana Gu