Variance between measures with a twist

DAX DAX Calculations
1

Hello,
I am doing this exercise in PowerPivot Model. I have over 100K items in my model. Avg_Net_Cost_23 is the cost of the item in the year 2023 and Avg_Net_Cost_24 is the cost of the item in the year 2024. It is possible for the cost of an item to be negative, zero, or positive depending on several factors.

My goal is to compute the variance between 2024 and 2023. I would like the formula to return a value of zero if either 2023 or 2024 has an item cost of zero. For instance, if Avg_Net_Cost_23 or Avg_Net_Cost_24 is zero, I would the formula to return zero. I would like to tweak the formula below to achieve this result. I have attached a screenshot of the pivot.

Sample image

Cost_Var2:=SUMX(
SUMMARIZE(Sheet1,Sheet1[ITEM],
“Cost24”, [Avg_Net_Cost_24],
“Cost23”, [Avg_Net_Cost_23]),
[Cost24]-[Cost23])

2

Hi @stevewulai - Can you try below formula.

Cost_Var2 = If(OR(max(Sheet1[Avg_Net_Cost_23]) = 0,max(Sheet1[Avg_Net_Cost_24]) = 0),0, SUMX(
SUMMARIZE(Sheet1,Sheet1[ITEM],
"Cost24", max(Sheet1[Avg_Net_Cost_24]),
"Cost23", max([Avg_Net_Cost_23])),
[Cost24]-[Cost23]))

Thanks
Ankit J

3

Hi ankit, thank you for your help…I forgot to mention that Avg_Net_Cost_23 and Avg_Net_Cost_24 are measures in my model and not regular columns, so the Max function did not work. But I went ahead to remove that piece (as shown below) and was able to obtain the desired result. However, the only issue now is the sum of individual rows matches the expected result but the grand total is showing something different. I know from previous video lessons by Sam, this formula can help fix grand total issues. Do you have any suggestions?

Cost_Var3:=IF(OR([Avg_Net_Cost_23]=0, [Avg_Net_Cost_24]=0),0,
SUMX(
SUMMARIZE(Sheet1,Sheet1[ITEM],
“Cost24”, [Avg_Net_Cost_24],
“Cost23”, [Avg_Net_Cost_23]),
[Cost24]-[Cost23]))

4

Hi @stevewulai - Should be something like this. If not working then share the sample PBIX file.

Cost_Var2_New = if(HASONEVALUE(Sheet1[ITEM]),If(OR([Avg_Net_Cost_23] = 0,[Avg_Net_Cost_24] = 0),0, [Avg_Net_Cost_24] - [Avg_Net_Cost_23]),SUMX(
SUMMARIZE(Sheet1,Sheet1[ITEM],
"Cost24", [Avg_Net_Cost_24],
"Cost23",[Avg_Net_Cost_23]),
[Cost24]-[Cost23]))

Thanks
Ankit J

1 Like
5

Sample File.pbix (39.0 KB)

Please find attached the sample PBI file…

6

Hi @stevewulai - What is the total are you expecting ? Is it 3.02 ?

Thanks
Ankit J

7

Yes, I am expecting the variance total to be $3.02.

8

Hi @stevewulai - Try below.

Cost_Var3_1 = IF(HASONEVALUE(ODP[ITEM]),IF(OR([Avg_Net_Cost_23]=0,[Avg_Net_Cost_24]=0),0,
[Avg_Net_Cost_24]-[Avg_Net_Cost_23]),
SUMX(
SUMMARIZE(ODP,ODP[ITEM],"test",
IF(OR([Avg_Net_Cost_23]=0,[Avg_Net_Cost_24]=0),0,
[Avg_Net_Cost_24]-[Avg_Net_Cost_23])
),[test]))

Thanks
Ankit J