DAX Dataset and KPI Creation Guide

DAX Formula for Data Set Creation

To create a dataset with 5 columns (a, b, c, d, e) and 2 rows where all values are 5, you can use the following DAX expression:

DAX Code to Create the Data Set

MyDataTable = 
DATATABLE(
    "a", INTEGER,
    "b", INTEGER,
    "c", INTEGER,
    "d", INTEGER,
    "e", INTEGER,
    {
        {5, 5, 5, 5, 5},
        {5, 5, 5, 5, 5}
    }
)

Explanation

• DATATABLE Function: This function allows the creation of a table with specified column names and types.
• Column Definitions: The columns are defined as "a", "b", "c", "d", and "e", each with an INTEGER type.
• Row Data: Two rows are created, each filled with the value 5 for all columns.

Creating KPIs from Customer and Sales Tables

Assuming you have two tables named Customer and Sales, here are five KPIs you can create that combine data from both tables:

KPI Definitions

1. Total Sales Amount

   Total Sales = SUM(Sales[Amount])

2. Number of Customers

   Number of Customers = DISTINCTCOUNT(Customer[CustomerID])

3. Average Sales per Customer

   Average Sales per Customer = 
   DIVIDE(
       [Total Sales],
       [Number of Customers],
       0
   )

4. Sales Growth Rate

   Sales Growth Rate = 
   DIVIDE(
       [Total Sales] - CALCULATE([Total Sales], PREVIOUSYEAR(Sales[OrderDate])),
       CALCULATE([Total Sales], PREVIOUSYEAR(Sales[OrderDate])),
       0
   )

5. Customer Acquisition Rate

   Customer Acquisition Rate = 
   DIVIDE(
       COUNTROWS(Customer),
       DATEDIFF(MIN(Customer[JoinDate]), MAX(Customer[JoinDate]), DAY),
       0
   )

Explanation of KPIs

• Total Sales: Calculates the total sales amount from the Sales table.

• Number of Customers: Counts unique customer IDs from the Customer table, providing insight into the customer base.

• Average Sales per Customer: Divides total sales by the number of customers to give an average sales figure per customer, utilizing the DIVIDE function for safe division.

• Sales Growth Rate: Compares the total sales for the current period with the total sales from the previous year to measure growth, using CALCULATE and PREVIOUSYEAR functions to retrieve the necessary context.

• Customer Acquisition Rate: Measures the rate of new customer acquisition based on the join dates. This KPI indicates how effectively new customers are being added over time.

Conclusion

By implementing the provided DAX formulas, you will not only create a simple dataset but also derive essential KPIs that provide insights into customer engagement and sales performance. For further learning, consider the resources available on the Enterprise DNA Platform to enhance your DAX expertise.