In this tutorial, I'll walk you through the creation in Power BI of a dynamic list with the Top N products and Other. You can see the result below, where the list is filtered based on the selected month. 

Load the external file in Power BI, for creating the top N table

For this example, I'll use a sample table with 3 columns: sales date, product sold and amount of sales. To follow along with the tutorial, you can download the sample data from here.

Below is what the table looks like once it's loaded.

Create a new table with all the products and additional row for other

The first step is to create a new table with a column having all the products plus 'Others'. The table can be created with DAX. For that, click on 'New Table' in the Modeling tab.

After clicking it, a text field will appear. You can then paste the following DAX code in your textbook:

Here is the explanation of the code: 

'Products table' is the name of the new table, you can choose any name you wish. 

The expression does three things: the VALUES function creates a list of all the products, the ROW function creates a new product called Other, and finally, UNION merges the product list with the other product.

You can check out the final list by clicking on ' Data Tab' on the left pane.

Create a Power BI measure with the value for top N products and Other

The final step is to create the values for this new table. 

Logic for Top N and Other

The logic is the following:
– if the product is in the Top N (top 10 in the example), show the sales for the product
– if the product is Other, show the total sales minus the sales of the Top 10 products
– for Total, show the total sales
– otherwise (if the product is not in the Top N), don't show it

Add a measure

This calculation will be saved in a measure. You create a new measure by clicking on New Measure in the Modeling tab.

You can paste the following formula for the Top N and Other products in the textbox. It's a long formula so I'll walk you through it.

Top N measure explanation

There are 2 parts to this formula: what comes before the RETURN (steps 1 through 4) and what comes after the RETURN (steps 5 through 7). 
Before the RETURN are intermediary calculations, and after RETURN is the logic that was described above.

Step 1 creates a table with the top 10 products, based on each product's sales. The next step uses the table created in step 1 and calculates the sum of the top 10 products.
Step 3 calculates the sales of the selected product, the total sales and the sales of other products.

Step 4 creates intermediary variables based on the product currently selected: 
– topProductSelected checks if the product selected is in the top 10,
– otherSelected checks if the selected product is Other,
– selectionIsTotal checks if the total is selected

Finally, steps 5 through 7 reuse the intermediary calculations above to implement the logic.

After pasted, you click on Commit and you have your formula!

Visualize your Top N and Other

You can now insert your visualization, remembering to choose the product and measure from your new product table instead of the initial table.
Power BI now shows your table with your Top N and other products!

Add a slicer

Once the visualization is in place, you can add a slicer and filter the new table as the Top N is completely dynamic!. The Top products and Other line will be recalculated automatically.

The slicer can be anything you wish and does not need to be months: it could be years, regions, customers.

Enjoyed the article? Subscribe to the newsletter and download the Power BI of the article for free.