Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Anonymous
Not applicable

Top N combine with visual filter

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

3 REPLIES 3
v-yulgu-msft
Microsoft Employee
Microsoft Employee

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.