Finding Average based on multiple criteria

[SatishBadiger]

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. 

[#2 · Accepted solution]

@SatishBadiger 

Here is a simple formula solution:

In E2:

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

[#3]

@SatishBadiger 

You can try the attached file with Power Query.

[#4]

Hi @SatishBadiger 

For DAX (Power Pivot)

Average Rate measure:

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