Forum Discussion
Variance From Average
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,
Hi,
Write these measures
C = SUM(Data[Cost])Average product cost = AVERAGEX(ALL(Data[Area]),[C])Variance = [C]-[Average product cost]Hope this helps.
4 Replies
I apologize, below is a better representation of the desired result.
Hi,
Write these measures
C = SUM(Data[Cost])Average product cost = AVERAGEX(ALL(Data[Area]),[C])Variance = [C]-[Average product cost]Hope this helps.
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,
You are welcome.
