cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

Calculating an average over multiple entries

Hello 

 The dummy of the data I have is shown below, I am trying to calculate the average project span which would be (20+10+20)/3. However, there are multiple entries for the same project, and when I try to use the normal average in the visuals, the number is incorrectly calculated. I have attempted to use the measure averagex(summarise... but this also does not work. 

 

In summary, how do I calculate the average over one column, when the value may be repeated multiple times in the column for different rows. 

I am still new to PowerBi so struggling to get to grips with everything. 

thanks

AMSto_0-1596618130209.png

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Calculating an average over multiple entries

Hi @AMSto , 

You could refer to my sample for details. If this is not what you want, please correct me and inform me your expected output.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

11 REPLIES 11
Highlighted
Super User IX
Super User IX

Re: Calculating an average over multiple entries

@AMSto , Try like

divide(sumx(values(Table[project]), max(Table[Project Span])),distinctCOUNT(Table[project]))

or

avergageX(summarize(Table[project],"_1", max(Table[Project Span])), divide(sum(_1]),count([_1])))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Community Champion
Community Champion

Re: Calculating an average over multiple entries

@AMSto 

Try like

Average_M = 
AVERAGEX(
    SUMMARIZE(
    TABLE,
    TABLE[PROJECT],
    "AVG", AVERAGE(TABLE[PROJECT SPAN])
    ),
    [AVG]
)

 

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

 

 

 

Highlighted
Regular Visitor

Re: Calculating an average over multiple entries

Hello thanks for replying

I have tried this and it doesn't quite work, I don't think the summation is based on the individual project span. I am trying to figure out how to sum all the project span per project. The distinct count part works but the sum part doesn't.

Highlighted
Community Champion
Community Champion

Re: Calculating an average over multiple entries

@AMSto 


Please share an example and provide sample data that we can copy.

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

Highlighted
Regular Visitor

Re: Calculating an average over multiple entries

Value calculated is incorrect

Highlighted
Community Champion
Community Champion

Re: Calculating an average over multiple entries

@AMSto 

What is your expected average based on the sample you provided?

 Please provide more clarity to your question.

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

Highlighted
Regular Visitor

Re: Calculating an average over multiple entries

I expect the average to be 16.67 based on the data I provided. 

The number of projects is 3, the sum of the project spans over these three projects is 50. 50/3=16.67.

I want to sum the total project span per the project and not sum the whole column, then divide by the number of distinct projects. 

so I want the sum of the projects span but for each project and not to add the duplicates. I don't want to have to remove duplicates as I am using the table structure for another calculation.

Highlighted
Community Champion
Community Champion

Re: Calculating an average over multiple entries

@AMSto 

So the formula I provided does the same right? I think the sample data you shared and the results you expect do not match. try to provide data that will help create the exact result that is in your mind.

Fowmy_0-1596623802960.png

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

 

Highlighted
Regular Visitor

Re: Calculating an average over multiple entries

I am not getting that result using the formula you provided, unfortunately.

Let me change my question

what measure could I use to sum the column project span so I get a value of 50?

So I want to sum based on distinct projects and not a sum of the total column.

 

This is really where I am struggling.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors