cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
LaingBennett
Helper III
Helper III

Displaying an Average of Data from a SharePoint List

Hi there,

 

I have built the following chart which is control via a DROPDOWN menu.

LaingBennett_0-1603289559226.png

The data table that this comes from looks like this. 

LaingBennett_1-1603289625379.png

What I want to do is get an average amount of the number of events. This would be a sum of the Total divide by the number of Years in the selected list. 

 

I am using the following to try and get the total but it does not return a number.

Text(Sum(Filter('2020 - Sale Historical Data by Year' , Event = Historic_Data_DD.Selected.Value)

Also, I don't know where to begin to get it to divide by the count of rows. 

 

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
RandyHayes
Super User
Super User

@LaingBennett 

 

Consider the following formula to resolve your average:

With({theData: Filter('2020 - Sale Historical Data by Year' , Event = Historic_Data_DD.Selected.Value)},

    Text(
        Sum(theData, Total) / CountRows(theData)
    )
)
        

 

I hope this is helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

4 REPLIES 4
RandyHayes
Super User
Super User

@LaingBennett 

 

Consider the following formula to resolve your average:

With({theData: Filter('2020 - Sale Historical Data by Year' , Event = Historic_Data_DD.Selected.Value)},

    Text(
        Sum(theData, Total) / CountRows(theData)
    )
)
        

 

I hope this is helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

Thank you very much - works perfectly. 

Sorry I forgot to mention how to you get the return value to round to a whole number? Currently it have 8 decimal places. 

@LaingBennett 

Use a format on your Text function:

With({theData: Filter('2020 - Sale Historical Data by Year' , Event = Historic_Data_DD.Selected.Value)},

    Text(
        Sum(theData, Total) / CountRows(theData),
        "0.0"
    )
)

Note, the "0.0" will give you whole number and one decimal position.  If you want no decimal, then change to "0"

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (3,262)