DAX KPI Dataset Creation

Objective

To create a DAX formula that generates a dataset with 5 columns and 5 rows by combining the 'Customer' table and 'Sales' table to produce 5 Key Performance Indicators (KPIs).

Data Structure

Assuming the following two tables:

1. Customer Table

   • CustomerID
   • CustomerName
   • Region

2. Sales Table

   • SalesID
   • CustomerID (foreign key)
   • SalesAmount
   • SalesDate

KPIs Definition

The following five KPIs will be defined:

1. Total Sales Amount
2. Average Sales Amount per Customer
3. Number of Unique Customers
4. Sales Growth Rate
5. Total Sales By Region

DAX Formulas

The dataset can be created using the following DAX measures:

1. Total Sales Amount

TotalSalesAmount = SUM(Sales[SalesAmount])

2. Average Sales Amount per Customer

AverageSalesPerCustomer = AVERAGEX(
    VALUES(Customer[CustomerID]),
    CALCULATE(SUM(Sales[SalesAmount]))
)

3. Number of Unique Customers

UniqueCustomerCount = DISTINCTCOUNT(Customer[CustomerID])

4. Sales Growth Rate

Assuming we measure growth between the current year and the previous year:

SalesGrowthRate = 
DIVIDE(
    [TotalSalesAmount] - 
    CALCULATE([TotalSalesAmount], SAMEPERIODLASTYEAR(Sales[SalesDate])),
    CALCULATE([TotalSalesAmount], SAMEPERIODLASTYEAR(Sales[SalesDate])),
    0
)

5. Total Sales By Region

TotalSalesByRegion = 
SUMMARIZE(
    Customer,
    Customer[Region],
    "TotalSales", SUMX(
        FILTER(Sales, Sales[CustomerID] = Customer[CustomerID]),
        Sales[SalesAmount]
    )
)

Example Implementation

To create a dataset with 5 columns and 5 rows that includes the specified KPIs, you can use the UNION function to combine measures with a table function.

KPIDataset = 
UNION(
    DATATABLE(
        "KPI", STRING,
        "Value", DOUBLE,
        {
            {"Total Sales Amount", [TotalSalesAmount]},
            {"Average Sales Amount per Customer", [AverageSalesPerCustomer]},
            {"Number of Unique Customers", [UniqueCustomerCount]},
            {"Sales Growth Rate", [SalesGrowthRate]},
            {"Total Sales By Region", [TotalSalesByRegion]}
        }
    )
)

Summary

• The dataset includes 5 columns for KPIs values and 5 rows reflecting the KPIs calculated based on relationships between the Customer and Sales tables.
• This structured approach enables effective monitoring of sales performance and customer engagement.
• For further insights and advanced DAX capabilities, consider exploring courses offered on the Enterprise DNA Platform.