In this article we will see how to display the TOP N and Others in Power BI, means to show the top n products and remaining products will be consolidated into single product and displayed as “Others”.
As you can see here we have a bar chart which displays total sales by products.
Lets take a look at table named financials that we are using as shown below.
Implementing TOP N and Others
As you can see, on X-axis we have taken products but now we have to show products and others on X-axis, for that we need to create a separate table which will have all unique products and others.
Lets create a table, go to Modeling tab and click on New table.
Now you can see, we have a table having all products and others.
Now we will create a relationship between products and financials table and relationship type will be one to many as shown below.
Now we will create a slicer to show the values for TOP N, user can change the values from slicer to see the top n products dynamically.
Lets quickly create a what- if parameter , for this go to Modeling tab then click on Numeric range under New Parameter tab.
Now you can see, a pop up window named Parameter opens, now you need to provide a details for Parameter as following:
Name: provide the name for parameter.
Datatype: data type for values in parameter.
Minimum, andย Maximumย value: specify the minimum and maximum value for parameter.
Increment: this is how much the parameter will adjust when interacted with in a report.
Default: is a default value for parameter.
Add Slicer to this Page: when this is checked, It puts a slicer with your what-if parameter onto the current report page automatically.
After providing details click on Create Button.
You can see, a slicer is added into report page also you can see a table named TOP N is created in data model.
Lets format the slicer and make it single select drop down list.
Now will create a measure that will calculate a Total Sales for TOP N ( value selected from slicer) products and remaining products sales will be accumulated in “Others”.
Now lets replace the chart’s X-axis and Y-axis, on X-axis replace the Product field with the Product field from UniqueProduct table to show the Products and Others on X-axis and replace the Total Sales measureย with the Top N Total Sales.
Once you done with this, Just select 3 from slicer and you will see that chart is showing total sales of TOP 3 products and total sales products other than top 3 is showing as Others.
As you can see, products are not showing in correct order, lets display the sales of top 3 products first than sales of Others.
For this, we need to create a measure that will assign a rank to products based on their sales.
Lets create a measure as shown below.