Average Sales Per Customer Calculation in DAX

This document outlines a DAX function designed to calculate the average sales per customer. The solution includes necessary components like imports, documentation, input validation, and a usage example.

DAX Function

// Average Sales Per Customer Calculation
Average Sales Per Customer = 
VAR TotalSales = SUM(Sales[SalesAmount]) // Summing up all sales amounts from the Sales table
VAR TotalCustomers = DISTINCTCOUNT(Sales[CustomerID]) // Counting unique customers from the Sales table
RETURN
    IF(
        TotalCustomers = 0, // Check for division by zero
        BLANK(), // Returns blank if there are no customers
        TotalSales / TotalCustomers // Calculate average sales per customer
    )

Explanation

• Imports: In DAX, there is no explicit import statement like in other programming languages. The function utilizes columns from the Sales table.

• Documentation:

  • Purpose: This function calculates the average sales per customer.
  • Parameters: The function does not take any parameters.
  • Return Type: Returns a numeric value or BLANK if no customers exist.
  • Exceptions: Handles division by zero gracefully by returning BLANK instead of an error.

• Input Validation:

  • The function checks if the TotalCustomers is zero. If it is, the function returns BLANK to prevent division by zero errors, ensuring robust performance under all conditions.

• Commentary:

  • SUM(Sales[SalesAmount]): This aggregates the total sales amount.
  • DISTINCTCOUNT(Sales[CustomerID]): This counts the number of unique customers.
  • IF: Used to handle cases where there are no customers.

Usage Example

Assuming you have a Sales table structured as follows:

You can create a measure using the function Average Sales Per Customer. Once the measure is created, you can add it to your report to show the average sales per customer.

Expected Output

For the above example:

• Total Sales = 200 + 300 + 100 + 400 = 1000
• Total Unique Customers = 3
• Average Sales Per Customer = 1000 / 3 ≈ 333.33

After creating this measure in Power BI or other DAX-compatible environments, you can visualize the results in your reports.

Conclusion

The provided DAX function offers a simple yet effective way to calculate average sales per customer while ensuring robustness and accuracy in the results. For further learning on DAX and data modeling, exploring resources on the Enterprise DNA platform is recommended.