Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
46 | |
28 | |
22 | |
12 | |
8 |
User | Count |
---|---|
76 | |
51 | |
44 | |
16 | |
12 |