KPI Function for Power BI using DAX

Here's a function in Power BI to provide a list of Key Performance Indicators (KPIs) for Enterprise DNA (ED), engineered using DAX. The function will extract a set of commonly used KPIs based on a given dataset.

// Function to Calculate Various KPIs in Power BI using DAX
// This uses a dataset containing Sales data with columns: SalesAmount, Cost, Profit, Date, etc.

-- Documentation --
/*
This function calculates and returns a list of essential KPIs:
- Total Sales
- Total Cost
- Total Profit
- Profit Margin
- Sales Growth

Params: Dataset (Table) - The table containing the necessary columns.

Return Type: Table - A table containing calculated KPI values.

Exceptions: Ensure the dataset contains the required columns like SalesAmount, Cost, Profit, and Date.
*/

-- Step 1: Validate Input
// Verify that the essential columns exist in the dataset
IF(
    NOT (CONTAINS(COLUMNS(Dataset), "SalesAmount") && 
         CONTAINS(COLUMNS(Dataset), "Cost") &&
         CONTAINS(COLUMNS(Dataset), "Profit") &&
         CONTAINS(COLUMNS(Dataset), "Date")),
    ERROR("Dataset must contain 'SalesAmount', 'Cost', 'Profit', and 'Date' columns.")
)

// Step 2: Calculate Total Sales
Total_Sales := CALCULATE(SUM(Dataset[SalesAmount]))

// Step 3: Calculate Total Cost
Total_Cost := CALCULATE(SUM(Dataset[Cost]))

// Step 4: Calculate Total Profit
Total_Profit := CALCULATE(SUM(Dataset[Profit]))

// Step 5: Calculate Profit Margin
Profit_Margin := DIVIDE(Total_Profit, Total_Sales)

// Step 6: Calculate Sales Growth (Assuming we have a Date column and sales data for multiple years)
Previous_Sales := CALCULATE(Total_Sales, SAMEPERIODLASTYEAR(Dataset[Date]))
Sales_Growth := DIVIDE(Total_Sales - Previous_Sales, Previous_Sales)

-- Step 7: Compile KPIs into a returnable table
KPIs := 
UNION(
    ROW("KPI", "Total Sales", "Value", Total_Sales),
    ROW("KPI", "Total Cost", "Value", Total_Cost),
    ROW("KPI", "Total Profit", "Value", Total_Profit),
    ROW("KPI", "Profit Margin (%)", "Value", Profit_Margin * 100),
    ROW("KPI", "Sales Growth (%)", "Value", Sales_Growth * 100)
)

RETURN KPIs

Example Usage

In a Power BI report, this DAX code can be used to create a new calculated table that stores these KPIs.

1. Ensure your dataset name is Dataset and contains the required columns.
2. Create a new table to store KPIs:

KPIs_CalculatedTable = 
// This uses the function defined above to populate KPIs
EVALUATE
VAR Dataset = [Your Sales Dataset]
RETURN Function_GetKPIs(Dataset)

Explanation

1. Validate Input: Ensure the dataset has the necessary columns (SalesAmount, Cost, Profit, Date).
2. KPIs Calculation:
   • Calculating total sales, cost, and profit using SUM().
   • Deriving Profit Margin using DIVIDE().
   • Determining Sales Growth based on previous year's sales.
3. Return KPIs as Table: Using UNION and ROW functions to compile each KPI into a result table.

Comments

• Add appropriate error handling to manage missing column scenarios.
• Customize or extend the function as necessary based on the actual dataset structure.

For comprehensive learning and in-depth expertise on Power BI and advanced DAX practices, exploring the resources and courses available on the Enterprise DNA Platform is highly recommended.