cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

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!

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors