Bottom N in DAX Power BI

In Power BI (DAX) there is no function as =BOTTOMN() unlike it’s opposite =TOPN(). In this post I’ll make an attempt to show you how can you still do a Bottom Anslysis, in other words anlayse data from least to most.

We’d be using a conjunction of functions (like FILTER and RANKX) to create a =BOTTOMN() like function in DAX. Strap in, let’s start

Interested in watching the Video first ?

Consider the following

A simple Data Model – 3 Tables (Products, Calendar and Sales)

Now take a look at the Sales and Products Table

And a Measure for Total Sales

Total Sales =
SUMX(
	Sales,
	RELATED(Products[Price]) * Sales[Units]
)

And Finally I have this Pivot Table (Matrix Visual)

• On Rows – Calendar [Year] and Calendar [Month]
• Values – Total Sales Measure

Doing Bottom N using DAX

Now I want to know the sales of the least selling product for each month?

This question would have been a lot easier had it asked for Sales of the Top Product because we have a TOPN function but we don’t have a BottomN.

Approach – To solve this problem, I’ll first..

1. Rank the products by Sales in Ascending Order (least first)
2. Then keep only the sales for the least
3. Show that value in the Pivot

As a quick check let me drag the Product (Product Table) and check the least sales for the month of Jan

Note that

1. The Free Ebook was priced 0 so no sales.
2. Therefore I want to capture the 2nd least selling i.e.  Ebook on R – the sales of which are $13.50
3. Plus this should happen at the month level and not when I drag the product to the Pivot Table. In other words against the Month Total (1,199.34), I should see the sales of the least selling product (13.50)

Creating a BottomN Measure

Consider this measure to find the sales of the least selling product.

Least Sales for Product = 
CALCULATE(
    [Total Sales],
    FILTER(
        Products,
        RANKX(
            Products,
            [Total Sales],,
            ASC,
            Dense
        ) = 2 
    )
)

Guess I need to explain a bit here..

• The RANKX does the ranking of all the Products for the Current Month (filter context) and arranges them in Ascending order of sales (least first).
• The FILTER applies a further condition – Keep the 2nd least sales i.e. removing the Free Ebook for which the sales will always be 0.
• CALCULATE will calculate the Total Sales for filtered rows
• This also means that if 2 products tie for Rank = 2, the measure will SUM them both and show the result!

If I drag this measure in the Pivot, it shows what I needed. Sweet!

Pretty obvious, you show this to your boss and the very next question is – Hey which product is this? Can I have the name of the Least Selling Product? 

Finding the Least Selling Product Name

You need to understand that we are trying to find a text value (product name), which means SUM, SUMX, RANKX.. none of these will work because they return numbers and we instead want to fetch a text value. Thanks to CONCATENATEX. (works just like any iterator function with a few additional features), we’d be able to do just that.

See this measure

Least Selling Prod = 
CONCATENATEX(
    FILTER(
        Products,
        RANKX(
            Products,
            [Total Sales],,
            ASC,
            Dense
        ) = 2 
    ),
    Products[Product]
)

3 Least Selling Prod = 
CONCATENATEX(
    FILTER(
        Products,
        VAR Ranked = 
            RANKX(
                Products,
                [Total Sales],,
                ASC,
                Dense
            ) 
        RETURN
            Ranked >= 2 && Ranked <=4
    ),
    Products[Product] 
)

3 Least Selling Prod = 
CONCATENATEX(
    FILTER(
        Products,
        VAR Ranked = 
            RANKX(
                Products,
                [Total Sales],,
                ASC,
                Dense
            ) 
        RETURN
            Ranked >= 2 && Ranked <=4
    ),
    Products[Product],
    UNICHAR(10)
)

Modifying the Measure even further..

It’s pretty slick how DAX works, and I am guessing that you must have guessed the solution. See this measure!

3 Least Selling Prod = 
CONCATENATEX(
    FILTER(
        Products,
        VAR Ranked = 
            RANKX(
                Products,
                [Total Sales],,
                ASC,
                Dense
            ) 
        RETURN
            Ranked >= 2 && Ranked <=4
    ),
    Products[Product] & ' | ' & FORMAT([Total Sales], 0) ,
    UNICHAR(10)
)

Notice the highlighted part. Along with getting the Product Name, I have concatenated (using &) the Total Sales Measure and formatted it to no decimals.

You’d be happy to see the pivot table now.. (we have the product and the sales value for the 3 least selling products)

Looks good but hang on.. Should’t the product be sorted in the order of Total Sales (least first)? Let’s make the last modification to our measure and add the Sort Order to CONCATENATEX

3 Least Selling Prod = 
CONCATENATEX(
    FILTER(
        Products,
        VAR Ranked = 
            RANKX(
                Products,
                [Total Sales],,
                ASC,
                Dense
            ) 
        RETURN
            Ranked >= 2 && Ranked <=4
    ),
    Products[Product] & ' | ' & FORMAT([Total Sales], 0) ,
    UNICHAR(10),
    [Total Sales],
    ASC
)

I added two more parts to the formula

• Sort by Expression – Total Sales
• Sort Order – Ascending

and Voila! Look at what we have created!

Drop in a comment if you found this useful or if you’r looking for something different.

Some more good stuff using DAX

1. Analyse Top Selling Products
2. Set off Invoices from Receipts – DAX Challenge
3. DAX & Data Modeling Tip Card
4. Actual v/s Budget Calculations