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
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
Super User

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@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!

 

 

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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