Indexing Using DAX

[yashmd]

Hi Everyone!

I'm facing a challenge to create a company-wide Index using DAX in PowerBi.

Please refer below as my Purchase data.

Item Code		Receipt Date		Unit Price		Qty		Value		UOM
ID_1		01-Apr-24		10		100		1000		KG
ID_1		02-Apr-24		11		100		1100		KG
ID_3		05-Apr-24		65000		20		1300000		TON
ID_2		02-May-24		120		10000		1200000		ECH
ID_1		05-May-24		14		220		3080		KG
ID_2		10-May-24		160		3000		480000		ECH
ID_3		18-May-24		67000		10		670000		TON
ID_2		06-Jun-24		130		15000		1950000		ECH
ID_1		07-Jun-24		20		300		6000		KG
ID_2		09-Jun-24		135		1000		135000		ECH
ID_2		12-Jul-24		155		23000		3565000		ECH
ID_3		21-Jul-24		62000		23		1426000		TON

The output that is expected would look be Weighted Average of the Index that would be calculated.

Step 1: Weighted Average Matrix. (Simple Weighted Average of an Item for that month)

		Apr-24		May-24		Jun-24		Jul-24
ID_1		10.5		14		20
ID_2				129.231		130.3125		155
ID_3		65000		67000				620

Step 2: Index of that Weighted Average. (Using base as 100, we calculate the Index100. Challenge is to make the first nonblank month as 100)

		Apr-24		May-24		Jun-24		Jul-24
ID_1		100		133.3333		190.4762
ID_2				100		100.8369		119.9403
ID_3		100		103.0769				95.38462

Step 3: Weighted Average of the Index (Now the last part to calculate the index of the entire company. Taking weights as the sum of value for that paticular month and item, we calculate Weighted Average) i.e. SUMX(Index*Value)/SUM(Value).

(For e.g. April-24 would be =((100*2100+100*1300000)/(2100+1300000)
     and   May-24 would be =((133.33*3080+100*1680000+103.0769*670000)/(3080+1680000+670000) and so on...

		Apr-24		May-24		Jun-24		Jul-24
ID_Index		100		100.9197		101.0941		112.9244

I'm facing difficulty when there is no entry for a paticular month, then the values become infinity.

Thanks,
Best Regards.

[123abc]

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.

[yashmd]

Dear 123abc,

Thank you for the solution provided.
The first 2 steps are working as per your formula. 
Although the formula for the last step is not working. The solution provided simply sums the enitre index as per VALUES(Itemcode).
I have tried using ALLExcept but it contradicts with the VALUES function.
If you can guide if it is possible with DAX, it would be helpful.

Thanks and Regards,
Yash Dhadke

[123abc]

To calculate the company-wide index correctly while considering individual items, you can use the following DAX measure:

Company Index =
VAR TotalValue = SUM('Purchase Data'[Value])
RETURN
DIVIDE(
SUMX(
ADDCOLUMNS(
SUMMARIZE('Purchase Data', 'Purchase Data'[Receipt Date]),
"WeightedIndex",
[Index100] * SUM('Purchase Data'[Value])
),
[WeightedIndex]
),
TotalValue,
0
)

This measure calculates the company-wide index by first creating a table that summarizes the data for each unique receipt date, calculates the weighted index for each date, and then sums up these weighted indexes. This approach ensures that the index is correctly calculated for each unique receipt date and then aggregated for the company-wide index.

With this measure, your company-wide index should be correctly calculated, taking into account individual items and their respective values for each month.

[yashmd]

Hi 123abc,

We have implemented your solution, but still are not able to get the desired result as the formula is not able to evaluate VALUES() function and populate a company wide index.
Please refer below screenshot.

Thank you for your help

Regards.