topic Indexing Using DAX in DAX Commands and Tips

Indexing Using DAX

Anonymous — Mon, 18 Sep 2023 10:50:28 GMT

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.

Re: Indexing Using DAX

123abc — Mon, 18 Sep 2023 13:24:39 GMT

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.

Re: Indexing Using DAX

Anonymous — Thu, 21 Sep 2023 09:25:30 GMT

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

Re: Indexing Using DAX

123abc — Fri, 22 Sep 2023 08:03:12 GMT

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.

Re: Indexing Using DAX

Anonymous — Mon, 25 Sep 2023 10:09:35 GMT

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.