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
AMSto
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
dax
Community Support
Community Support

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
dax
Community Support
Community Support

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.

Fowmy
Super User
Super User

@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

 

 

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

AMSto
Regular Visitor

Value calculated is incorrect

@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

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

AMSto
Regular Visitor

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.

@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

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

AMSto
Regular Visitor

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.

@AMSto 

The following measure shows the correct average 16.77. If you wanna see the 50 total,  uncomment //SUMEX... and comment _AVG2 to check.

You can download the file: HERE

Average_M = 
VAR _AVG1 = 
    SUMMARIZE(
    'Table',
    'Table'[PROJECT],
    "AVG", AVERAGE('Table'[PORJ SPAN])
    )
VAR _AVG2 = 
    AVERAGEX(
        _AVG1,
        [AVG]
    )

RETURN

//SUMX(_AVG1,[AVG])
_AVG2

 

________________________

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

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

amitchandak
Super User
Super User

@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])))

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.

@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

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

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.