cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Post Prodigy

## 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

1 ACCEPTED SOLUTION
Community Champion

Try this:

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

And you get this:

In doing so, you are also helping me. Thank you!

Proud to be a Super User!

2 REPLIES 2
Post Prodigy

Hi Paul

thank you for your quick reponse it works fine

Community Champion

Try this:

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

And you get this:

In doing so, you are also helping me. Thank you!

Proud to be a Super User!