Adding a Top 10 filter in Power Bi is quite easy but, the drawback with a Top 10 filter is that it is a Visual level filter and we cannot add this to all the other visuals in the report. Also, this Top 10 filter is a static filter and doesn’t give the end user to analyze any Top numbers. So, let’s add a Top N Filter to Power Bi and make it interactive for all the other Visuals in the Report.
Step- 1:
When you have your data model ready, go to the Report View and Click on Enter Data. This will open a new dialogue box for you where you can specify the numbers that you want your end users to have for TOP N and Click on Load.
Step- 2:
Once you have done this you can go to Relationship view and see this new table there. But there should not be any relationship between this table and other tables that you had initially in the Relationship. This table is an independent table without any relationship with any of the data tables.
Since I am working with a demoralized table (Superstore Data) here, you can see I have 2 tables – 1 is my data table and the other one is the new created table for me. Also, this new table will be found in the Report view Fields pane.
Step- 3:
Go to the Report view now and create a Bar Chart Visualization of Region and Sales, like below:
Now, bring the Selected number from the Selected Top N ( the new table), to the sheet and give it a Slicer visualization, like below :
From the Slicer dropdown I have given the Slicer a List appearance. When you select any number in the Slicer, you will find that there is no change happening on the tree map that we have created.
So, now we have to work on the interactivity.
Step- 4:
Create a new measure with the following formula:
Selected N = SELECTEDVALUE(‘Selected Top N'[Selected Number])
Now, drop a Card in the Visual and add this new measure to the card and notice that the values change as we change the Slicer.
But the Bar chart Visual is still not having any effect.
So, we have to add this new measure to the calculation that will give us TOP N Products per Sales.
Step- 5:
Now, click on the Sales by Region Bar chart and from the Values section where you can see Sales field like below:
Replace this with the new created measure Sales of Top N Products with the following formula:
Sales of Top N Products = CALCULATE([Total Sales], TOPN([Selected N], Orders, [Total Sales], DESC,Orders[Product Name]))
Now, you will notice that the bars show you the Top N Products contribution per Region. The Top N changes according to the numbers that you pick up from the slicer.
Let’s go to the formatting of the sheet and Format the visualization properly. I have also created a new measure for dynamic title and added it in the visualization as a card. The formula for the dynamic title is as below:
Top N text = “Top ” & ‘Selected Top N’[Selected N] & ” Product Contribution per Region“
