Forum Discussion
Advocate IVFinding Average of a Calculated Measure
Hello,
I'm creating a Report that provides managers and directors their performance ratings. For managers, these ratings are determined by a formula based on the requirements that they have to complete Task A at least 3 times and have a total task count (Total_Count = Task A + Task B) of 14,16 or 18 for the different rating tiers (2,3,4). Anyone that does not meet those criteria get a rating of 1. Here's the Measure I use to perform this calculation:
Manager_Rating = IF( [Total_Count]<14,1,IF( [TaskA_Count]<3, 2, IF( [Total_Count ]<16, 2, IF( [Total_Count]<18,3,4))))
That's easy enough and works perfectly. The problem is that the directors are then rated by averaging the rating of the managers that report to them. I can't figure out how to capture the Managers' ratings in order to average them. Everything I try just rolls up the task counts to the directors and puts those numbers through the formula, giving every director a perfect rating.
I'm sure there is an easy solution, but I'm a self-taught developer and this one is elluding me. Any help is greatly appreciated!!
Example of current table
| Rating | |
| Director 1 | |
| Manager 1A | 4 |
| Manager 1B | 2 |
| Manager 1C | 3 |
| Director 2 | |
| Manager 2A | 4 |
| Manager 2B | 3 |
| Manager 2C | 3 |
| Director 3 | |
| Manager 3A | 4 |
| Manager 3B | 4 |
| Manager 3C | 3 |
Example Data (there are a LOT more columns, but I believe only this is needed for this question)
| Director | Manager | Task Type |
| Director 1 | Manager 1A | Task A |
| Director 2 | Manager 2A | Task B |
| Director 3 | Manager 3B | Task B |
| Director 2 | Manager 2C | Task A |
- Hi
Try with
Director_rating=averagex(values(table1[managers]), [manager rating])
2 Replies
- Zubair_Muhammad
Community Champion
Hi
Try with
Director_rating=averagex(values(table1[managers]), [manager rating])
jhartranft60Thanks! Believe it or not, I'd actually already tried this, but after reading your reply and trying again, I realized that I'm an idiot. I'd copied the table from a different tab (It's the exact same, minus hierarchy and a filter), but never cleared the filter that was needed on the other tab, but not this one.
I've been staring at this for a few hours and your post gave me the confidence to let me know my original formula was right all along and I just needed to troubleshoot elsewhere!