topic Re: Per row variance in a table against the same single measure in DAX Commands and Tips

Per row variance in a table against the same single measure

Ry5 — Wed, 31 Jan 2024 06:10:00 GMT

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

Re: Per row variance in a table against the same single measure

ValtteriN — Wed, 31 Jan 2024 07:32:36 GMT

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

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

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/

Re: Per row variance in a table against the same single measure

Ry5 — Wed, 31 Jan 2024 07:37:09 GMT

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]