Power BI makes it fairly easy to implement top N filters on your visuals using the filter pane. You can quickly add the dimension you want to apply the top N filter to, select the number of items to display, and choose the column or measure for the calculation. However, what if you need to calculate a top N by multiple dimensions instead of just one? For instance, instead of showing the top 5 products by sales, you might want to display the top 5 products for each region and/or for each year by sales. Or perhaps you’ve already used a top N filter for a specific workaround, such as implementing a single date picker as we discussed in a previous blog, and now you need an additional top N statement for your analysis.
Is it as simple as adding another Top N filter to the filter pane? Unfortunately, it’s not. Power BI will display an error message stating that “A top N filter is already applied.” So, how can we implement multiple top N filters or a top N filter using multiple dimensions?
Problem
Let’s focus on the goal of displaying the best-selling product by region and year. By utilizing the Top N filter from the filter pane, we can identify the best-selling product:
However, if we add country and year to the table, something unexpected happens. We only see one entry per region and year, which might suggest that the top N filter worked. But in reality, the best-selling product was calculated beforehand, and only this product is displayed for each combination of country and year. You’ll notice that the product remains the same for each row:
Solution
To resolve this issue, we need to create a measure using the RANKX() function, which will assign a rank to each row in our table. Our goal is to generate a new ranking (starting from 1) for every combination of region and year. The measure to achieve this looks like this:
RANK Sales by product region year =
RANKX(
ALLSELECTED(Sales[Product]),
[Sales],
,
DESC,
Skip
)The ALLSELECTED() function removes any filters applied to the Sales[Product] column while preserving filters on other columns. This allows us to respect the filters from the year and region columns for a given row while opening up the filter context for the product column to calculate the rank. We can add the measure to the table to see the results:
Notice that after the combination of France, 2019, the ranking starts from one again. By utilizing this new Rank column in our table, we can now use the filter pane to display only the rows with a rank of 1:
Using the same logic, we can display the top 3 products (or any other number) for each year and region by simply changing the filter to “Rank is less than or equal to 3”. If you prefer not to display the rank in your table, you can remove the column, the filter from the filter panel will be sufficient to narrow down your table to the desired rows.
More about Power BI: