Forum Discussion
Top N combine with visual filter
Microsoft EmployeeHi 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
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