DAX Function to Calculate Percentage of Measures

This document outlines a DAX function that calculates the percentage of three specified measures against the total of a particular field. This approach helps in analyzing how these measures contribute to the overall total.

Main Components

• Input Measures: The three measures whose percentages we want to calculate.
• Total Field: The total value against which the percentage will be computed.
• Return Value: A decimal representing the percentage.

DAX Code

PercentageOfMeasures = 
VAR TotalFieldValue = SUM('YourTable'[YourField]) // Total of the field
VAR Measure1Value = [Measure1] // First measure
VAR Measure2Value = [Measure2] // Second measure
VAR Measure3Value = [Measure3] // Third measure
VAR CombinedMeasureValue = Measure1Value + Measure2Value + Measure3Value // Sum of the three measures

RETURN
IF(
    TotalFieldValue = 0, 
    BLANK(), // Avoid division by zero
    (CombinedMeasureValue / TotalFieldValue) * 100 // Calculate percentage
)

Explanation of the Code

1. Variable Definitions:

   • TotalFieldValue: Calculates the total of a specified field using the SUM function.
   • Measure1Value, Measure2Value, Measure3Value: Captures the values of the three measures.
   • CombinedMeasureValue: Sums the three measures to get their total value.

2. Return Statement:

   • The IF statement checks if TotalFieldValue is zero to prevent division by zero errors, returning BLANK if true.
   • If not, calculates the percentage of the combined measure against the total field value and multiplies by 100 to express it as a percentage.

Input Validation

• Division by Zero Handling: The code handles division by zero gracefully by checking whether TotalFieldValue is zero.

Usage Example

Suppose you have a sales table with quantities sold in SalesQuantity, and three measures defined:

• Total Sales (the total sales amount)
• Online Sales
• In-Store Sales

You can create the PercentageOfMeasures to analyze how much the combined online and in-store sales contribute to the total sales:

Measure1 = SUM(Sales[OnlineSales])
Measure2 = SUM(Sales[InStoreSales])
Measure3 = SUM(Sales[TotalSales])

PercentageOfSales = 
VAR TotalSalesValue = SUM(Sales[SalesQuantity])
VAR OnlineSalesValue = [Measure1]
VAR InStoreSalesValue = [Measure2]
VAR CombinedSalesValue = OnlineSalesValue + InStoreSalesValue

RETURN
IF(
    TotalSalesValue = 0, 
    BLANK(), 
    (CombinedSalesValue / TotalSalesValue) * 100
)

Best Practices

• Use descriptive names for your measures and variables to enhance readability.
• Ensure to encapsulate any complex calculations in variables for better performance and clarity.
• Regularly validate the results with real data to ensure accuracy in reporting.

---

For advanced learning and in-depth practices in DAX and data analysis, consider exploring the Enterprise DNA Platform, which offers a range of courses tailored for all skill levels.