DAX Formulas for KPIs Combining Customer and Sales Tables

Introduction

In this section, five key performance indicators (KPIs) will be created using DAX by combining data from a customer table and a sales table. The KPIs include Total Sales, Average Sales per Customer, Customer Count, Sales Growth, and Repeat Customers.

Assumptions

• Customer Table: Contains columns like CustomerID, CustomerName, etc.
• Sales Table: Contains columns like SalesID, CustomerID, Amount, SaleDate, etc.

The relationships between the tables will be assumed as follows:

• The CustomerID is the common field linking the Customer and Sales tables.

KPIs

1. Total Sales

This KPI calculates the total sales amount.

DAX Formula:

Total Sales = SUM(Sales[Amount])

2. Average Sales per Customer

This KPI computes the average sales for each customer based on the total sales divided by the total number of unique customers.

DAX Formula:

Average Sales per Customer = DIVIDE(SUM(Sales[Amount]), DISTINCTCOUNT(Customer[CustomerID]), 0)

3. Customer Count

This KPI counts the total number of distinct customers.

DAX Formula:

Customer Count = DISTINCTCOUNT(Customer[CustomerID])

4. Sales Growth

This KPI calculates the percentage growth in sales compared to the previous month.

DAX Formula:

Sales Growth = 
VAR CurrentMonthSales = SUM(Sales[Amount])
VAR PreviousMonthSales = CALCULATE(SUM(Sales[Amount]), PREVIOUSMONTH(Sales[SaleDate]))
RETURN DIVIDE(CurrentMonthSales - PreviousMonthSales, PreviousMonthSales, 0)

5. Repeat Customers

This KPI determines the number of customers who have made more than one purchase.

DAX Formula:

Repeat Customers = COUNTROWS(FILTER(VALUES(Customer[CustomerID]), CALCULATE(COUNTROWS(Sales)) > 1))

Explanation of Formulas

1. Total Sales

• Function: SUM
• Logic: Aggregates total sales from the Amount column in the Sales table.

2. Average Sales per Customer

• Function: DIVIDE, SUM, DISTINCTCOUNT
• Logic: Calculates total sales and divides by the count of unique customers. The DIVIDE function prevents division errors by returning 0 if the denominator is zero.

3. Customer Count

• Function: DISTINCTCOUNT
• Logic: Counts unique entries in the CustomerID column.

4. Sales Growth

• Function: VAR, CALCULATE, PREVIOUSMONTH, DIVIDE
• Logic: Captures current month sales and computes the difference with the previous month’s sales, returning the percentage growth.

5. Repeat Customers

• Function: COUNTROWS, FILTER, VALUES, CALCULATE
• Logic: Filters customers who made multiple purchases and counts them.

Practical Illustration

Assume the Customer table consists of customers A, B, and C, and the Sales table contains records of purchases made by these customers. By using the above DAX formulas in a Power BI report, it would allow the visualization of overall sales performance, customer engagement metrics, and business growth indicators effectively.

Conclusion

These DAX formulas will facilitate the calculation of key metrics essential for analyzing customer-related performance in sales data. For further learning on DAX and data modeling, consider exploring resources available on the Enterprise DNA platform.