Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Hello,
I have a table listing an ID, its area location and a number.
I have then created a measure to calculate the average for Area A.
ID | Area | Reading |
1 | A | 3.8 |
1 | A | 3.1 |
2 | A | 3.9 |
3 | B | 5.8 |
3 | B | 5.1 |
4 | B | 8.2 |
4 | B | 8.1 |
5 | B | 8.7 |
6 | C | 6.3 |
Average of A = 3.6
I now need to calculate the variance for every row against that average of A to show a table like this:
ID | Area | Reading | Variance |
1 | A | 3.8 | 0.2 |
1 | A | 3.1 | -0.5 |
2 | A | 3.9 | 0.3 |
3 | B | 5.8 | 2.2 |
3 | B | 5.1 | 1.5 |
4 | B | 8.2 | 4.6 |
4 | B | 8.1 | 4.5 |
5 | B | 8.7 | 5.1 |
6 | C | 6.3 | 2.7 |
How do I calculate the variance for each row against that same single measure?
Thank you
Solved! Go to Solution.
I solved it.
I was trying to do the whole variance calculation in one measure using variables which wasn't working.
The way to do it is create a measure for the average of A (with an ALL statement), a second measure for the average of all rows, and then a third measure minusing the first two measures.
Average of A =
I solved it.
I was trying to do the whole variance calculation in one measure using variables which wasn't working.
The way to do it is create a measure for the average of A (with an ALL statement), a second measure for the average of all rows, and then a third measure minusing the first two measures.
Average of A =
Hi,
Here is one way to do this:
If you want to keep the Area A now matter what here is modified dax:
I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!
My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/
Proud to be a Super User!
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
10 | |
9 | |
8 | |
7 | |
6 |
User | Count |
---|---|
14 | |
13 | |
11 | |
9 | |
8 |