# Showing The Last Three Customer Sales Using The TOPN Function

I’m going to cover some great functions here in Power BI that are very useful across a variety of analytic scenarios. Some of these functions can help us work out the last three customer sales. You may watch the full video of this tutorial at the bottom of this blog.

What I want to show you is how to work out the sales from a specific customer, but by only looking at the last three sales rather than all of their sales.

In order to do this in a dynamic way inside the formulas, I need to structure them so that the filtering naturally occurs for every single context that the formula is applied to.

This is where it can get a little more advanced inside Power BI. I had to start utilizing functions which create filtered virtual tables to generate the results that I need. Thus, I need to work out how to create a filtered table of only the last number of sales, which in this case was three.

## Sample Report Showing The Last Three Customer Sales

In this sample report, you’ll find interesting insights about how to structure tables or table functions inside Power BI. Iâ€™ll be showing the last three sales of a customer and showing it by product using a chart.

First, you can adjust the time frame here in the date selector. Secondly, I have added a customer selector here where you can select any customers or group of customers.

After that, I want to evaluate their last three product purchases and find out how much those product sales are.

The goal for this tutorial is to dynamically work out the last three products that the selected customers purchased. Somehow, I need to rank the sales by purchase date and figure out the last three products. Furthermore, I also want to calculate the total sales for all these last three purchases.

## Calculating The Last Three Customer Sales

Letâ€™s look at the formula for computing the last three sales of the customers.

I need to work out what were the last three purchases of the customer by using the TOPN function. It is inside an iterating function so it will go through rows and tables. But instead of going over an actual table, it will iterate through a virtual table determined by some ranking.Â

Basically, thatâ€™s what TOPN does to find the three last sales based on the purchase date. Additionally, the DESC function sorts the results in descending order.

Within any particular context, Iâ€™ve created a table of all the purchases that were made using the SUMMARIZE function.

To sum it up, when I select a customer, the formula iterates through every single purchase that the customer has made. Next, itâ€™s going to evaluate the purchase date, and then rank the purchase dates accordingly in descending order. Lastly, the TOPN function returns a virtual table of only the last three purchases.

After figuring out the last three purchases, Iâ€™m going to calculate the three rows and count the total sales.

Now, the calculation I made is a bit tricky. Iâ€™m gonna show you how this could go wrong especially if you take out the CALCULATETABLE function.

When I drag the Last 3 Sales Wrong formula into the table, youâ€™ll see that I got results for every single row for this particular table.

But you can see in the bottom part, the total for the Last 3 Sales column is correct. But the total under the Last 3 Sales Wrong column is incorrect.

This is where you can test your deep understanding of the context. How do you think the results under Last 3 Sales Wrong are showing up?

A basic explanation is that every single product is filtering a particular result. If a product is selected, the formula is going to find the last three purchases of the product. But all the products are evaluated at the table and itâ€™s going to add them up.

For example, if I select Product 5. Youâ€™ll see that itâ€™s always going to rank number one because itâ€™s the only one in the context. It will be evaluated in the table and itâ€™s going to be calculated for total sales.

So in the formula, I need to do wrap the SUMMARIZE function inside CALCULATETABLE so that itâ€™s going to remove the context of the product in the particular results. As a result, it’s going to evaluate through every single product for the selected period. Then, itâ€™s going to rank based on all those sales using TOPN. If itâ€™s not present, itâ€™s going to rank in the top three because there aren’t enough sales for the particular data set.

Moreover, I have put this table on the right for a reason.

I need to evaluate through the entire table versus the filtered table. The table above evaluates the entire table to the correct result. As you can see, it ends with the last three sales from the 22nd of June, 7th of March, and 27th of August in 2017. Then, it counts the particular sales under the Total Sales column.

In the Last 3 Sales By Product Name table, you can see a visualization for the top three products and its corresponding sales.

You can also select any other customers or group of customers and the results will change dynamically.

This is certainly an advanced tutorial. But hopefully you understand how great the insight is when you combine a lot of these DAX formulas. You can quickly see the last three customer sales by product and put it into a visualization

## Conclusion

As you can see in the formula, there are a lot of functions within a function. These are functions like TOPN, CALCULATETABLE, and SUMMARIZE.

What I’ve done is I’ve combined all of these functions into one formula. I’ve combined them all to enable us to extract this great insight.

This is why DAX formulas are immensely powerful. If you understand these functions, you can put all these together and get amazing results. Historically, it wasnâ€™t possible by using such tools like Excel. But being able to combine all these together using Power BI and create incredible visualizations, you can achieve the desired result.

If you want to learn more about what can be achieved with other techniques just like this one, check out the Business Analytics Series module at Enterprise DNA On-demand.

I hope you enjoy going through this process.

Sam

## Microsoft Power Query Tutorial On How To Fix Mixed Fixed Column Width Issues

Mudassir: For today, we have a very interesting problem to work with. The problem with this file is...

## MultiIndex In Pandas For Multi-level Or Hierarchical Data

MultiIndex in Pandas is a multi-level or hierarchical object that allows you to select more than...

## Power BI DAX ALL Function – How It Works

I want to give you a really quick introduction to the Power BI DAX ALL function. I find that there can...

## Sales Vs Budgets Insights â€“ Extended Budget Allocation Formula

In this tutorial, Iâ€™m going to show you an extended version of the ultimate Budget Allocation...

This blog will show you some of the best tools and tricks to quickly create excellent visuals for your...

## Power BI Percent Of Total – Using CALCULATE Statement

Power BI percent of the total is a really common calculation that we require quite often. This is great...

## Power Query Data Types And Connectors

This tutorial will talk about Power Query data types and connectors. You'll learn what data types are...

## Power BI Custom Calendars: Calculating For Month On Month Change – 445 Calendars

This tutorial is about how you can run time intelligence calculations over custom calendars in Power...

## DAX CALCULATETABLE Vs FILTER Function

Both CALCULATETABLE and FILTER are powerful tools for manipulating and analyzing data in DAX query, but...

## Setting Up A Dynamic StartDate And EndDate For Power Query Date Tables

I'll show you how to set up dynamic start dates and end dates using Power BIâ€™s power query date...

## Bookmarks In Power BI – Grouping by Report Page

I'm going to show you how I utilize bookmarks in Power BI. For example, if I have multiple bookmarks...

## Power Query Features: An Overview

This tutorial will discuss about the available features inside the Power Query Editor. You'll learn how...