Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi
I have a facts table with the following coliumns - Property Ref, Date & Spend.
I also have a calendar tabe with the following Date & Year
Facts Table
Property Ref | Date | Spend |
18 | 02/06/2017 | £240 |
18 | 03/01/2018 | £318 |
18 | 07/07/2019 | £341 |
18 | 11/08/2020 | £875 |
2121 | 03/01/2018 | £2,385 |
2121 | 11/08/2020 | £3,013 |
2121 | 15/09/2021 | £95 |
2650 | 02/06/2017 | £24 |
2650 | 03/01/2018 | £1,975 |
2650 | 07/07/2019 | £1,041 |
2650 | 11/08/2020 | £54 |
2650 | 15/09/2021 | £9 |
The Spend column is a total of all spend over a 5 year period. I have created a simple measure to average the spend over 5 years
Spend = SUM(FactsTable[Spend])
Avg Repairs 5 years =
DIVIDE([Spend],5
)
However when I produce a visual table I am happy with each row apart from the total as the total 'Average' column is not outputing the resultsI would like
Ideally I would like the total 'Avg Repairs 5 years' to calculate like this
Total Spend / number of rows
The expected result would = £691.33
Hope this makes sense
Richard
Solved! Go to Solution.
Try this:
Aver spend Final =
AVERAGEX ( VALUES ( FactTable[Property Ref] ), DIVIDE ( [Sum spend], 5 ) )
And you get this:
Proud to be a Super User!
Paul on Linkedin.
Hi Paul
thank you for your quick reponse it works fine
Try this:
Aver spend Final =
AVERAGEX ( VALUES ( FactTable[Property Ref] ), DIVIDE ( [Sum spend], 5 ) )
And you get this:
Proud to be a Super User!
Paul on Linkedin.
User | Count |
---|---|
130 | |
108 | |
101 | |
67 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |