 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.

3 Replies
best response confirmed by SatishBadiger (New Contributor)
Solution

# Re: Finding Average based on multiple criteria

Here is a simple formula solution: In E2:

=AVERAGEIFS(\$D\$2:\$D\$6,\$A\$2:\$A\$6,A2,\$B\$2:\$B\$6,B2)

# Re: Finding Average based on multiple criteria

You can try the attached file with Power Query.

# Re: Finding Average based on multiple criteria

For DAX (Power Pivot) Average Rate measure:

``=CALCULATE ( AVERAGE ( Table1[Rate / Hour] ), ALL ( Table1[Department] ) )``