TOP N and Others in Power BI

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 write a DAX formula as shown below.

UniqueProducts =

UNION (
    DISTINCT ( financials[Product] ),
    DATATABLE ( "Product", STRING, { { "Others" } } )
)

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”.

Lets create a measure as shown below.

Top N Total Sales =

VAR TOPN_Products =
    TOPN (
        'TOP N'[TOP N Value],
        ALLSELECTED ( UniqueProducts[Product] ),
        [Total Sales]
    )

VAR TOPN_Products_Sales =
    CALCULATE (
        [Total Sales],
        KEEPFILTERS ( TOPN_Products )
    )

VAR Other_Products_Sales =
    CALCULATE (                  
        [Total Sales],
        ALLSELECTED ( UniqueProducts[Product])
    )

        - CALCULATE (                        
            [Total Sales],
            TOPN_Products
         )

RETURN

    IF (                                   
        SELECTEDVALUE ( UniqueProducts[Product] ) = "Others",
        Other_Products_Sales,
        TOPN_Products_Sales
       )

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.

Sorting Order =

IF (
   [Top N Total Sales]
        <> BLANK (),
    RANKX (
        TOPN (                                        
            'TOP N'[TOP N Value],
            ALLSELECTED ( UniqueProducts[Product]),
            [Total Sales]
        ),
        [Total Sales],      
        ,
        DESC,
        DENSE
    )
)

Now, lets drag the Sorting Order measure into the tool tip as shown below.