Forum Discussion
Getting an average where columns match
Hi there friends,
I have data like below:
| IDPerson | Job type | Job option | Job category | Hourly rate | Average rate |
| 134 | Type 1 | Option 1 | On-site | 60 | 58,33 |
| 135 | Type 2 | Option 1 | On-site | 55 | 58,33 |
| 136 | Type 1 | Option 1 | On-site | 75 | 58,33 |
| 137 | Type 4 | Option 4 | Remote | 40 | 58,33 |
| 138 | Type 1 | Option 3 | Remote | 60 | 58,33 |
| 139 | Type 5 | Option 1 | On-site | 60 | 58,33 |
What would be the best way, in DAX, to get the average based on matching columns? So instead of a general average of the hourly rate calculated over all rows, i need the average to be calculated based on matching columns (aside from the person ID). So the average in the first row should be calculated for rows where job type = Type 1, job option = Option 1, category = On-site.
What's the best way to accomplish this? I'm stumped.
Cheers in advance!
Hi JGroothedde - create a below calculated column that dynamically calculates the average for each row based on the specified conditions
calculated column:
Average rate =CALCULATE(AVERAGE('Avger'[Hourly rate]),ALLEXCEPT('Avger', 'Avger'[Job type], 'Avger'[Job option], 'Avger'[Job category]))Hope it helps
Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!
2 Replies
Hi JGroothedde - create a below calculated column that dynamically calculates the average for each row based on the specified conditions
calculated column:
Average rate =CALCULATE(AVERAGE('Avger'[Hourly rate]),ALLEXCEPT('Avger', 'Avger'[Job type], 'Avger'[Job option], 'Avger'[Job category]))Hope it helps
Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!Thank you so much for getting me out of this, for some reason I kept trying to do this in a measure. Thanks!
