cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

Individual average divided by Team Average

 

 I'm really new to Power BI and really need some help here.

 

I'm just wondering if it is possible to create a calculation that gets the "Average Completed Request per day" for each employee divided by the total average as shown on the image below. For example on row 1 which Gied with 5.78 average should be divided to 3.57 (average of the whole lined in red), resulting to 162%.

 

two.png

 

If possible, below result is what I want to achieve. Really much easier computation in Excel.

three.png

Thank you so much in advance. 

 

 

 

 

 

 

 

 

 

 

 

4 REPLIES 4
Anonymous
Not applicable

Re: Individual average divided by Team Average

The formula you're looking for is technically "average of completed requests per day per team member", so you can take your existing formula as-is, and divide by a distinct count of team member names. That will count up the number of distinct names in each context, so for each row, you should get the same number (dividing by one), and you'll see the accurate number in the bottom.

Re: Individual average divided by Team Average

@Anonymous

But the problem here is that the "Average Completed Request per day" is a measure and not a column. Also, I made a measure like you said

Productivity = [Average Completed Request per day] / DISTINCTCOUNT(TblNewSLATEST[EmployeeName] and it only takes the 24.74 average as the numerator divided by the DISTINCTCOUNT of the employees resulting to 2.24. 

 

What I need is the average of the average inside the red rectangle on the first image above which must be 3.57.

 

I could actually get the result 3.57 something like this:

Productivity = SUM([Average Completed Request per day]) / DISTINCTCOUNT(TblNewSLATEST[EmployeeName])

 

But it won't work since "Average Completed Request per day" is a measure and not a column.

 

Thanks!

 

 

 

Re: Individual average divided by Team Average

@SonnyChilds

But the problem here is that the "Average Completed Request per day" is a measure and not a column. Also, I made a measure like you said

Productivity = [Average Completed Request per day] / DISTINCTCOUNT(TblNewSLATEST[EmployeeName] and it only takes the 24.74 average as the numerator divided by the DISTINCTCOUNT of the employees resulting to 2.24. 

 

What I need is the average of the average inside the red rectangle on the first image above which must be 3.57.

 

I could actually get the result 3.57 something like this:

Productivity = SUM([Average Completed Request per day]) / DISTINCTCOUNT(TblNewSLATEST[EmployeeName])

 

But it won't work since "Average Completed Request per day" is a measure and not a column.

 

Thanks!

Re: Individual average divided by Team Average

I am trying to create a measure that will give me an index average on each row.

Beside the "NewAverage" I need a measure named as "Productivity" that will divide each row to the column total value. By using the "NewAverage", the first row would essentially have an index average of 1.62 (5.78/3.57) the second row would have an index average of 1.42 (5.09/3.57).

 

 

 

five.PNG

Below are some reference:

Request Completed = COUNTROWS(TblNewSLATEST)

 

Number of days = DISTINCTCOUNT(TblNewSLATEST[Month&Day])

 

Average Completed Request per day = DIVIDE([Request Completed], [Number of days])

 

NewAverage = AVERAGEX(SUMMARIZE(TblNewSLATEST, TblNewSLATEST[EmployeeName], "toAverage", [Request Completed]), [Average Completed Request per day])

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors