Calculating Average of Average help

[Rsanjuan]

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?

[Greg_Deckler]

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

[Rsanjuan]

Hi @Greg_Deckler,

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

PM Rating = Average(Job[PM Score])

[Greg_Deckler]

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.

[Greg_Deckler]

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

[Rsanjuan]

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

[BhaveshPatel]

@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

[Rsanjuan · 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!

[Greg_Deckler]

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

[v-haibl-msft]

@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