Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.