DAX is Percentage < OR > than total Average

[cottrera]

Hi 

I have a facts table with property reference, Address, K.P.I  measures , scores. Each property has multiple K.P.I measures so the properties is repeated multiple times.

Property Reference	Address	Measure	Score
1	A	K.P.I-1	50
1	A	K.P.I-2	100
1	A	K.P.I-3	75
2	B	K.P.I-1	75
2	B	K.P.I-2	10
2	B	K.P.I-3	0
3	C	K.P.I-1	0
3	C	K.P.I-2	100
3	C	K.P.I-3	100
4	D	K.P.I-1	100
4	D	K.P.I-2	250
4	D	K.P.I-3	100

I would like a DAX function that SUMS the scores by property

Property ReferenceAddressSum of Score1234

A	225
B	85
C	200
D	450

I then need to know the total AVERAGE of the above aggregation 

Avg Score

240

The finaly part of the DAX function would be, to show a if the aggregated Score is Greater > or Lower< than the total average score.

Property Reference	Address	Sum of Score	Avg Score	Difference	% < or > Average
1	A	225	240	-15	-6.25%
2	B	85	240	-155	-64.58%
3	C	200	240	-40	-16.67%
4	D	450	240	210	87.50%

Hope this makes sense

thank you

Richard

[cottrera]

Thank you for your quick response

[Avantika-Thakur · Accepted solution]

Hi @cottrera ,

You will be required to make the below 4 measures to get the required results :

1) ReferenceAddress Sum of score = CALCULATE(SUM('Table'[Score]),ALLEXCEPT('Table','Table'[Address]))

2) Avg Score = calculate(AVERAGEX('Table',[ReferenceAddress Sum of score]),ALL('Table'[Address]))
3) Difference = [ReferenceAddress Sum of score] - 'Table'[Avg Score]

4) % vs Avg score = ([Difference]/'Table'[Avg Score])*100

And we will get the below results -

Please Accept the solution if it answers you query.

Thanks,

Avantika