Skip to main content
cancel
Showing results for 
Search instead 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

Reply
cottrera
Post Prodigy
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 RefDateSpend
1802/06/2017£240
1803/01/2018£318
1807/07/2019£341
1811/08/2020£875
212103/01/2018£2,385
212111/08/2020£3,013
212115/09/2021£95
265002/06/2017£24
265003/01/2018£1,975
265007/07/2019£1,041
265011/08/2020£54
265015/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

cottrera_0-1630921781199.png

 

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
PaulDBrown
Community Champion
Community Champion

Try this:

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

And you get this:

Result.JPG

 

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

2 REPLIES 2
cottrera
Post Prodigy
Post Prodigy

Hi Paul

 

thank you for your quick reponse it works fine

PaulDBrown
Community Champion
Community Champion

Try this:

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

And you get this:

Result.JPG

 

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Helpful resources

Announcements
PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors