Forum Discussion
Solved
Finding Average based on multiple criteria
Hi every one, I'm trying to write a formula based on multiple conditions as per stated below.
| Location | Designation | Department | Rate / Hour |
| IND | Data Analyst | A | 10 |
| IND | Data Analyst | B | 15 |
| US | Software Engineer | B | 25 |
| IND | Software Engineer | C | 12 |
| US | Data Analyst | A | 20 |
I have a similar table given above.
Problem Statement:
If there are multiple designations(same designation) per location, then I need the averaged rate based on Location & Designation. For example: For location IND and Designation Data Analyst we have different rates based on department. What I would need is to have rate to be averaged based on Designation and location. For instance here I want (10+15)/2 = 12.5 [Location IND & Designation Data Analyst].
Expected Output:
| Location | Designation | Department | Rate / Hr |
| IND | Data Analyst | A | 12.5 |
| IND | Data Analyst | B | 12.5 |
| US | Software Engineer | B | 25 |
| IND | Software Engineer | C | 12 |
| US | Data Analyst | A | 20 |
It would be great if someone could give solutions to DAX, PowerQuery as well if this can be done in those tools.
Here is a simple formula solution:
In E2:
=AVERAGEIFS($D$2:$D$6,$A$2:$A$6,A2,$B$2:$B$6,B2)
For DAX (Power Pivot)
Average Rate measure:
=CALCULATE ( AVERAGE ( Table1[Rate / Hour] ), ALL ( Table1[Department] ) )You can try the attached file with Power Query.
Here is a simple formula solution:
In E2:
=AVERAGEIFS($D$2:$D$6,$A$2:$A$6,A2,$B$2:$B$6,B2)
