Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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]))
Thank you for your quick response
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]))