Calculate the Subtotal of a Power Bi Measure Correctly

[ftaimur]

I am calculating a % Contribution based on something called a Vector Variance Score. The Vector Variance Score takes the square of the difference between two items e.g Payment in a year compared to the prior year. That squared difference is then divided by the sum of all squared differences.

In my example, the "Total_Length" is supposed to be a subtotal of the Length (squared differences) column, however that subtotal is not coming out correct. The Total Length is supposed to be 36,135,349,765,756 in this case, however currently it is being evaluated incorrectly as the squared delta between the Pmt_2012 and Pmt_2011 subtotals rather than the total sum of the Length measure. Can someone please help with what I am doing incorrect here? 

Here is how I calculated the Length and Total Length measures: 

Length = ([Pmt_2012]-[Pmt_2011])^2   -> This is calculating correctly

Total_Length = CALCULATE([Length], ALL(Data[Supplier])) -> This subtotal is coming out incorrect

Contribution = [Length] / [Total_Length] -> This will get fixed once the Total_Length is corrected. 

[AlexisOlson]

If you want to sum over the suppliers individually and then total, then you need to iterate over each supplier for the subtotal.

Try this measure:

Total_Length = SUMX ( ALL ( Data[Supplier] ), [Length] )

[ftaimur]

Thank you Alexis. The Total_Length subtotal is correct, but for some reason, the total of the Contribution table does not add to 100%. Unsure why that is the case. 

[AlexisOlson · Accepted solution]

[Length] is the problem now.

Actually, it probably would have been better to change [Length] than [Total_Length].

Length_Supplier =
SUMX ( VALUES( Data[Supplier] ), ( [Pmt_2012] - [Pmt_2011] ) ^ 2 )

Total_Length_Supplier =
CALCULATE ( [Length_Supplier], ALL ( Data[Supplier] ) )

Contribution = [Length_Supplier] / [Total_Length_Supplier]

[ftaimur]

Thank you very much! This works. 

[smpa01]

@ftaimur  try one of Measure1 or Measure2 depending on your situation

Measure1 = 
VAR _X1= SUMX('Table', ('Table'[Y1]-'Table'[Y2])^2)
RETURN _X1

Measure2 = 
VAR _X1= SUMX('Table', ([Measure 2]-[Measure 3])^2)
RETURN _X1

Measure 2 = SUM('Table'[Y1])
Measure 3 = SUM('Table'[Y2])

Measure3 = CALCULATE([Measure2],ALL('Table'))

[AlexisOlson]

It's not clear that iterating at the row level of the 'Table' will produce the desired result. Granularity is important to consider with non-additive measures.