Variance From Average

[curtismob]

Hello.

I am trying to find the variance of a measure (SUM), from an average measure of the sum.  Below is some simple data.

Product	Area	Cost
Product1	Area 1	10
Product1	Area 2	15
Product1	Area 3	20
Product2	Area 1	20
Product2	Area 2	25
Product2	Area 3	30
Product3	Area 1	30
Product3	Area 2	35
Product3	Area 3	40

The SUM measure may be CostSum = SUM(Table[Cost])

Desired matrix result:

	Area 1 Cost	Avg	Variance	Area 2 Cost	Avg	Variance	Area 3 Cost	Avg	Variance
Product1	10	15	-5	15	15	0	20	15	5
Product2	20	25	-5	25	25	0	30	25	5
Product3	30	35	-5	35	35	0	40	35	5

I hope this data is simple enough to copy and paste to Excel/csv and import into a pbix.  The main challenge is having the product average be the same, for each area in the row.

Thank you in advance,

@curtismob 

[Ashish_Mathur · Accepted solution]

Hi,

Write these measures

C = SUM(Data[Cost])

Average product cost = AVERAGEX(ALL(Data[Area]),[C])

Variance = [C]-[Average product cost]

Hope this helps.

[curtismob]

Hi Ashish,  I am reviewing your solution and trying to test it with some actual data.  As you would expect, my actual data, with a couple of relationships involved, is not as simple as my example data for this post.  Your solution has given me some good direction.

Thank you for the quick response,

@curtismob

[Ashish_Mathur]

You are welcome.

[curtismob]

I apologize, below is a better representation of the desired result.