Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join 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.

Reply
Ry5
Helper I
Helper I

Per row variance in a table against the same single measure

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.

IDAreaReading
1A3.8
1A3.1
2A3.9
3B5.8
3B5.1
4B8.2
4B8.1
5B8.7
6C6.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:

IDAreaReadingVariance
1A3.80.2
1A3.1-0.5
2A3.90.3
3B5.82.2
3B5.11.5
4B8.24.6
4B8.14.5
5B8.75.1
6C6.32.7


How do I calculate the variance for each row against that same single measure?
Thank you

1 ACCEPTED SOLUTION
Ry5
Helper I
Helper I

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 = 

CALCULATE(
AVERAGE( 'Table'[Reading]),
'Table'[Area] = "A",
ALL'Table'[ID] ) )

All Rows Average =
AVERAGE( 'Table'[Reading])

Variance =  [All Rows Average] - [Average of A]

View solution in original post

2 REPLIES 2
Ry5
Helper I
Helper I

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 = 

CALCULATE(
AVERAGE( 'Table'[Reading]),
'Table'[Area] = "A",
ALL'Table'[ID] ) )

All Rows Average =
AVERAGE( 'Table'[Reading])

Variance =  [All Rows Average] - [Average of A]
ValtteriN
Super User
Super User

Hi,

Here is one way to do this:

Measure 15 = var _average = CALCULATE(AVERAGE('Table (22)'[Reading]),ALLEXCEPT('Table (22)','Table (22)'[Area])) RETURN

SELECTEDVALUE('Table (22)'[Reading])-_average



ValtteriN_0-1706686239103.png

If you want to keep the Area A now matter what here is modified dax:

Measure 15 = var _average = CALCULATE(AVERAGE('Table (22)'[Reading]),ALL('Table (22)'),'Table (22)'[Area]="A") RETURN

SELECTEDVALUE('Table (22)'[Reading])-_average

ValtteriN_1-1706686354269.png

 





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/





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.