Creating a Power BI Top Customers Report

Most businesses will have sales and marketing departments as well as top-level C-Suite/director levels. They are all going to be interested in a bunch of similar reports from organization to organization.

One of the most important reports, that sales and marketing teams are going to be interested in is:
Who are our best customers?

Top customers often contribute a significant amount of revenue to any organization. The 80/20 rule, that 80% of the revenue is made up of 20% of customers is quite common. Identifying top customers allows teams to segment their audience. Top customers can be given special treatment, such as dedicated account managers and special offers.

In the model from the Contoso database we use here, the DimCustomer table is joined to the FactOnlineSales table, and the FactOnlineSales table is joined to the Date table. We use the FactOnlineSales table rather than the FactSales table as the FactOnlineSales table as it includes a CustomerKey we can join to the DimCustomer table and pull out customer data at the person level (the FactSales table appears to include store-level data only).

To start creating the report, we can drag in the SalesAmount field from the FactOnlineSales table and the FirstName, LastName, and CustomerKey from the DimCustomer table as well as DateFirstPurchased. These are some of the fields a sales team is going to be interested in and they will likely ask for more to be added as time goes by.

Adding the Year field as a filtered visual from the data table, which is already joined to the FactOnlineSales table by the unique date key, allows us to filter the sales amount by year (and we could add months for example, as required).
We filter the report at the person level by adding the CustomerType field in the Filters panel:

You will notice here I just dragged in the SalesAmount field from the FactOnlineSales table, but that’s a bit lazy and we should create a measure for the field, like this:

OnlineSales = SUM(FactOnlineSales[SalesAmount])

The sales amount filter is useful, but teams are likely to be interested in several different sales calculations, which we can calculate using additional measures. For example, we might want to see the current year-to-date sales and the lifetime sales of a customer in the same report as follows:

Calculating Lifetime Sales with DAX

OnlineSales Lifetime = CALCULATE(SUM(FactOnlineSales[SalesAmount]), REMOVEFILTERS('Date'))

Using REMOVEFILTERS(‘Date’), removes all filters applied from the Date table, such as the year that is being used in the drop-down in this report, so the SalesAmount will be calculated for the customer’s lifetime. This now makes the report more interesting. The year filter still applies to the OnlineSales Measure, but does not apply to the OnlineSales Lifetime measure as below:

Comparing year-to-date vs. previous year-to-date is another common request.
To calculate year-to-date sales amount, we use the CALCULATE() function again, but this time combined with the DATESYTD() function:

Calculating Year-to-Date and Previous Year-to-Date Sales with DAX

To calculate Year to date sales we can use the DATESYTD function with CALCULATE:

OnlineSales YTD = CALCULATE(SUM(FactOnlineSales[SalesAmount]), DATESYTD('Date'[Date]))

and to calculate the OnlineSales YTD Prev Year, we can wrap the DATESYTD() function with the SAMETIMELASTYEAR() function as follows:

OnlineSales Prev YTD = CALCULATE(SUM(FactOnlineSales[SalesAmount]), SAMEPERIODLASTYEAR(DATESYTD('Date'[Date])))

Adding these measures to our report table, we should now have something looking like this :

Here we now have a top customer report for 2009, we can see which customers have brought in the highest revenue this year vs. last year to date, so we are comparing equal periods. We can also see customers’ lifetime value. In this example, Jon Yang is our best online customer, with $61K in online sales. This is equal to his lifetime sales. As you will notice, their first Date of Purchase is 22nd July 2001, which is a long time ago, but the database doesn’t have a record of this transaction as 2009 YTD sales equal lifetime sales. Presumably, this data was not recorded historically, which is often the case with older databases. The data you work with is often not perfect and we need to be aware of these specifics.

Now we have this year’s YTD measure and the previous year’s YTD measure, it is easy for us to calculate the variance % (or difference), between the two years.

Online Sales Var YTD% = ([OnlineSales YTD]-[OnlineSales Prev YTD])/[OnlineSales Prev YTD]

We have an infinity number for the first customer as they had no sales in the previous year, we can tidy that up using the DIVIDE() function, which automatically handles errors and infinity to tidy up the code.

Online Sales Var YTD% = DIVIDE(([OnlineSales YTD]-[OnlineSales Prev YTD]),[OnlineSales Prev YTD])


Let’s now add the month as an additional filter. As you can see the YTD and Prev YTD, are recalculated to show the online sales amount up until the end of the month selected. That’s useful and pretty powerful.

In a real-world scenario, sales teams are going to want the most up-to-date information, so we want to look to add previous days’ sales, week-to-date sales, and month-to-date sales. The issue here is that our data is old, so we need to do some juggling first to bring the Contoso sales data into the future to use for experimentation.
The process of adding a future date is outlined here.

DAX Previous Day Sales Calculation

Once we have the future date in our FactOnlineSales table as in the link above and join it to the date table, our report is brought into the future and we can create a measure calculating yesterday’s sales amount:

OnlineSales Yesterday = CALCULATE(SUM(FactOnlineSales[SalesAmount]), PREVIOUSDAY('Date'[Date]))

As you can see I can now filter on the current year, which is 2023, and remove the month filter.
I can now see the top customers for yesterday if I sort by the OnlineSalesYesterday field.
This is now giving the sales teams up-to-date, daily reporting, so they can keep their finger on the pulse of the business:

Now we’re using more ‘current’ data, we can now see we need Week-to-Date and Month-to-Date measures.

Calculating Week-to-Date Sales can be found here


Comments

Leave a Reply

Your email address will not be published. Required fields are marked *