DAX Average Total

[cottrera]

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

[cottrera]

Hi Paul

thank you for your quick reponse it works fine

[PaulDBrown · Accepted solution]

Try this:

Aver spend Final =
AVERAGEX ( VALUES ( FactTable[Property Ref] ), DIVIDE ( [Sum spend], 5 ) )

And you get this: