DAX Function for Contribution Percentage Calculation

This document describes a DAX function designed to calculate the contribution percentage of a specific field within a dataset. The contribution percentage shows how much a particular value contributes to the total of a related set of values.

Function Overview

Purpose

To compute the contribution percentage of a selected field against the total values of that field.

Parameters

• ValueField: The numeric field for which to calculate the contribution percentage.
• TotalField: The total numeric field for calculation comparison.

Return Type

• A decimal number representing the contribution percentage.

Exceptions

• DIVIDE BY ZERO: If the total of the TotalField is zero, this will return BLANK().

Function Definition

Contribution Percentage = 
VAR TotalValue = SUMX(ALL(TableName), [TotalField]) 
VAR CurrentValue = SUM([ValueField]) 
RETURN 
    IF(
        TotalValue = 0, 
        BLANK(), 
        DIVIDE(CurrentValue, TotalValue, 0)
    )

Code Explanation

• SUMX(ALL(TableName), [TotalField]): This calculates the total of the specified TotalField across all records, ignoring filters.
• SUM([ValueField]): This computes the current total of the specified ValueField based on the current context.
• IF(TotalValue = 0, BLANK(), ... ): Checks if the total is zero to prevent division by zero errors.
• DIVIDE(CurrentValue, TotalValue, 0): This safely calculates the division and returns 0 in case of a divide by zero scenario.

Usage Example

To use this function, first, create measures for ValueField and TotalField in a relevant table, for example, Sales and TotalSales. Then, you can call this function in your DAX environment:

Total Sales = SUM(Sales[SalesAmount])
Contribution Percentage = 
VAR TotalValue = SUMX(ALL(Sales), [TotalSales]) 
VAR CurrentValue = SUM(Sales[SalesAmount]) 
RETURN 
    IF(
        TotalValue = 0, 
        BLANK(), 
        DIVIDE(CurrentValue, TotalValue, 0)
    )

Example Scenario

In a sales report, if you wish to see how much each product contributes to total sales, you would define SalesAmount as the ValueField and calculate the Contribution Percentage measure to display those contributions.

Conclusion

The DAX function for contribution percentage provides a powerful way to analyze data by showing the individual impact relative to the whole. This code can be adapted for various scenarios by changing the ValueField and TotalField as per your specific dataset requirements. For more in-depth learning on DAX and advanced analytics, consider exploring courses offered on the Enterprise DNA Platform.