cancel
Showing results for
Did you mean:
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

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

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?

1 ACCEPTED SOLUTION
Helper IV

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!

9 REPLIES 9
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])`

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

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

Proud to be a Super User!

Helper IV

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

PM Rating = Average(Job[PM Score])

Super User IV

`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.

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

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

Proud to be a Super User!

Super User IV

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

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

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

Proud to be a Super User!

Helper IV

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

Community Champion

@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.
Helper IV

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!

Super User IV

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

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

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

Proud to be a Super User!

Microsoft

@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

Announcements