DAX Pattern: Top X and Others

As a Business Intelligence Consultant working with Power BI, I often get the following question:

“Could you give me a top X of our highest selling products?”

Of course the answer to this request is yes because that’s fairly easy to implement in Power BI. However, every once in a while we get the following follow-up question:

“Can you also add an ‘Others’ category to this top 5?”

This seems like a fair thing to ask but when you look at the options Power BI provides us it is not as straight forward as you would think.

If you want to look at it from a pure statistical point of view a calculated column would suffice. In our case though it should be as dynamic as possible with filters that affect our final result.

As with most requests in the BI field we can quite easily give the customer what they want by using a bit of DAX sprinkled with some creativity.

Our final result will look like this:

Great! So how do we set this up?

In our example we will use Products and their revenue.

We will be needing the following:

• A Calculated Table: This tables includes all Product_IDs, their Name and an additional Category ‘Others’
• Measure 1: The Sum of the Revenue
• Measure 2: The Sum of the Revenue where we treat the IDs of the products in the original table as the IDs of the new table.
• Measure 3: The Logic where we show the top 5 products and the grouped ‘Others’

1. First, we will start by creating the calculated table. Here we will store all the Products including an extra row with ID -1000 and ‘Others’.

We do this by using a union on the fields PRODUCT_WID and NAME from DIM_PRODUCT with a new row that uses -1000 for the PRODUCT_WID and ‘Others’ as the NAME.

This will enable us to show the Others category in the visual.

Below you can see the code we have used for our example. 

ProductTable =
UNION (
    SUMMARIZE ( 'DIM_PRODUCT', DIM_PRODUCT[PRODUCT_WID], DIM_PRODUCT[NAME] ),
    ROW ( "PRODUCT_WID", -1000, "NAME", "Others" )
)

(Note: this is not a standard table as it is not a part of the general flow of our model. It is created on the fly in DAX with no relationships pointing from or to it.) 

2. Now that we have out calculated table we will need to create a sum of the measure we want to use in the visual. In our case it will be the Revenue.

Sum Revenue =
SUM ( Fact_Sales[Revenue] )

3. The next step is to create a measure that is the Sum for the revenue but here we use the TREATAS function. This will make sure that whatever our top 5 is, the new product table will be filtered.

Sum Revenue Top 5 =
CALCULATE (
    [SUM REVENUE],
    TREATAS ( VALUES ( ProductTable[Product_ID] ), 'DIM_PRODUCT'[Product_ID] )
)

4. The final measure that we need to create makes the distinction between the Top 5 Products with the most revenue and all others.

This is the most complex of the three steps so we will split this up in the two variables and what the measure returns. (Below you can find the full DAX query)

Measure 3 =
/*
Variable 1: This variable will create a temporary table with all the Products and their revenue in descending order. Finally a Top 5 is taken from this table to have a list of the 5 Products with the highest revenue.
*/
VAR _Top5Products =
    SELECTCOLUMNS (
        TOPN (
            5,
            ADDCOLUMNS (
                VALUES ( DIM_PRODUCT[PRODUCT_ID] ),
                "Revenue", FACT_SALES[SUM REVENUE]
            ),
            [REVENUE], DESC
        ),
        "PRODUCT_ID", [PRODUCT_ID]
    ) /*
Variable 2: 
The second Variable will use the first variable to calculate the Revenue of all Products that are not in the ‘Top 5 Products’.
*/
VAR _OthersValue =
    CALCULATE ( [Sum Revenue], NOT ( 'DIM_EMPLOYEE'[EMPLOYEE_WID] IN _Top5People ) )
RETURN
    IF (
        COUNTROWS (
            FILTER (
                VALUES ( 'EmployeeDummy'[EMPLOYEE_WID] ),
                [Employee_WID] IN _Top5People
            )
        ) >= 1,
        [Sum Revenue Top 5],
        IF ( SELECTEDVALUE ( 'EmployeeDummy'[Employee_WID] ) = -1000, _OthersValue )
    )

The actual return result is a simple nested if that uses the previous two variables and the measure we created called ‘Sum Revenue Top 5’.

If the Product is in the Top 5 list of products it will return the Sum Revenue Top 5 per product.

If the Product is not in the Top 5 then it will return our second variable that is a sum of all the products minus the Top 5.

Finally, this measure will look like this:

Top 5 & Other =
VAR _Top5Products =
    SELECTCOLUMNS (
        TOPN (
            5,
            ADDCOLUMNS (
                VALUES ( DIM_PRODUCT[PRODUCT_ID] ),
                "Revenue", FACT_SALES[SUM REVENUE]
            ),
            [REVENUE], DESC
        ),
        "PRODUCT_ID", [PRODUCT_ID]
    )
VAR _OthersValue =
    CALCULATE ( [Sum hours], NOT ( 'DIM_EMPLOYEE'[EMPLOYEE_WID] IN _Top5People ) )
RETURN
    IF (
        COUNTROWS (
            FILTER (
                VALUES ( 'EmployeeDummy'[EMPLOYEE_WID] ),
                [Employee_WID] IN _Top5People
            )
        ) >= 1,
        [Sum Revenue Top 5],
        IF ( SELECTEDVALUE ( 'EmployeeDummy'[Employee_WID] ) = -1000, _OthersValue )
    )

5. Now that we have everything set up we only need to do one more thing and that is create the visual you would like.

In our case we will use Power BI’s Donut Chart.

You will need to use the NAME from the calculated Product Table and the Top 5 & Others measure.

Finally you will end up with a result like this.

As you can see, in PowerBI a lot can be solved by using some logic with your DAX.