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.

After that, sort this axis as shown below, select Sorting Order by ascending as shown below.

Now you can see, chart is showing top 3 products first than others.

Now, we need to format the Title as well. It should display the number of products dynamically based on values selected in TOP N slicer.

Lets create a measure for that, and after that we will set the conditional formatting for the title.

Title =

“Top ” & SELECTEDVALUE(‘TOP N'[TOP N]) & ” products sales “

Now, go to Format visual tab, then select General tab after that expand the Title and click on Conditional formatting (fx).

Now, select the measure Title in field and click on OK button.

Lets see the TOP 4 products sales, just select 4 from slicer and you can see the title is showing TOP 4 products sales.

Also Read..

Creating a dynamic title based on slicer selection

What- if parameter

Implementing TOP N and Others

Related

Leave a ReplyCancel reply

TOP N and Others in Power BI

Implementing TOP N and Others

Share this:

Related

Leave a ReplyCancel reply

Discover more from SQL BI Tutorials