Forum Discussion
DAX Average Total
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
Try this:
Aver spend Final = AVERAGEX ( VALUES ( FactTable[Property Ref] ), DIVIDE ( [Sum spend], 5 ) )And you get this:
2 Replies
Try this:
Aver spend Final = AVERAGEX ( VALUES ( FactTable[Property Ref] ), DIVIDE ( [Sum spend], 5 ) )And you get this:
Hi Paul
thank you for your quick reponse it works fine
