cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Rsanjuan
Helper IV
Helper IV

Calculating Average of Average help

Hi,

 

I have a scenario where I want to compute the total average of one person and another person based on what is selected on a slicer.   Here is an example:

 

1.  Selected one person where the total average PM rating is 4.65

Capture1.JPG

 

2.  Selected another person where the total average PM rating is 4.82

Capture2.JPG

3.  I want to get the average of those two average totals, but it's still taking the number of jobs into account.  I just want to have an average of the average (so 4.65+4.82/2) = 4.735.  Is this possible?

 

Capture3.JPG

1 ACCEPTED SOLUTION

@BhaveshPatel and @Greg_Deckler,

 

I was able to figure out the DAX expression for this:

 

AvgofAvgPM = AverageX(Values(Job[Project Manager]),Job[PM Rating])

 

Basically taking a unique value in the table project manager, and then getting the average value of each project manager.  Then it takes those values and just averages it.  

 

Thanks!

 

 

View solution in original post

9 REPLIES 9
Greg_Deckler
Super User IV
Super User IV

Is your PM Rating a Measure or are you selecting a PM Average column and use the quick calc to get the average displayed?

 

If it is a column, you could create a Measure that is effectively:

 

Average = AVERAGE([PM Rating])

---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hi @Greg_Deckler,

 

It's actually a measure, where Job is the table.

 

PM Rating = Average(Job[PM Score])

 

 

How about this Measure?

 

Average PM Rating = [PM Rating] / COUNTROWS(FILTERS(HoursWorked[Person]))

Basically, FILTERS returns a table of the values of the filters, we count the number of rows and divide the existing measure by that count.  Not sure if this gets you there but something along these lines perhaps. The problem is that you can't really aggregate Measures but I *think* this will work for your use case.

 

 


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Scratch that, that formula doesn't work. The problem is aggregating a measure.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@Greg_Deckler  Is there a way to put the total average for each PM in another table, and then take that average?

 

 

 

 

@Rsanjuan

 

In your scenario, as the PM Rating is measure, we are not able to use AVERAGE function to return average values directly. We need to create a new column with the same DAX which is used by "PM Rating" measure. Then we can create a new measure with AVERAGE function.

 

 

 

Thanks & Regards,

Bhavesh

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.

@BhaveshPatel and @Greg_Deckler,

 

I was able to figure out the DAX expression for this:

 

AvgofAvgPM = AverageX(Values(Job[Project Manager]),Job[PM Rating])

 

Basically taking a unique value in the table project manager, and then getting the average value of each project manager.  Then it takes those values and just averages it.  

 

Thanks!

 

 

View solution in original post

Nice, I didn't think of using VALUES, nice call.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@Rsanjuan

 

If you can get your problem solved by the last DAX expression you posted, you can mark your last post as answer to close this thread.

 

Best Regards,

Herbert

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors