Indexing Using DAX

To achieve the desired result of calculating a company-wide index in Power BI using DAX, you can follow these steps. The key challenge is handling cases where there is no entry for a particular month, which can result in division by zero errors. To overcome this challenge, you can use conditional logic to check for valid data before performing calculations.

Here's a step-by-step approach:

Step 1: Weighted Average Matrix

To calculate the weighted average matrix for each item and month, you can use DAX measures like this:

WeightedAverage = VAR TotalValue = SUM('PurchaseData'[Value]) RETURN SUMX('PurchaseData', [Unit Price] * [Qty] / TotalValue )

Create one measure like this for each month. These measures will calculate the weighted average for each item and month.

Step 2: Index of Weighted Average

To calculate the index based on the weighted averages with the first non-blank month as 100, you can use the following DAX measure:

Index100 = VAR FirstNonBlankMonth = CALCULATE( MIN('Calendar'[Month]), FILTER( ALL('Calendar'), [WeightedAverage] > 0 ) ) RETURN IF(ISBLANK([WeightedAverage]), BLANK(), [WeightedAverage] / CALCULATE([WeightedAverage], 'Calendar'[Month] = FirstNonBlankMonth) )

This measure calculates the index based on the weighted average, and it checks for a non-blank weighted average to avoid division by zero.

Step 3: Weighted Average of the Index

To calculate the company-wide weighted average of the index, you can use this DAX measure:

CompanyIndex = VAR TotalValue = SUM('PurchaseData'[Value]) RETURN SUMX(VALUES('PurchaseData'[Item Code]), [Index100] * [Value] / CALCULATE(SUM('PurchaseData'[Value]), VALUES('PurchaseData'[Item Code])) )

This measure calculates the weighted average of the index for the entire company while considering valid data points. It divides the sum of the product of index and value by the sum of the value for each item.

By using these measures, you should be able to calculate the weighted average matrix, the index based on the first non-blank month as 100, and the company-wide weighted average of the index while handling cases where there is no entry for a particular month without resulting in infinite values.