Finding Average based on multiple criteria

New Contributor

Hi every one, I'm trying to write a formula based on multiple conditions as per stated below. 

LocationDesignationDepartmentRate / Hour
INDData AnalystA10
INDData AnalystB15
USSoftware EngineerB25
INDSoftware EngineerC


USData AnalystA20

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: 

LocationDesignationDepartmentRate / Hr
INDData AnalystA12.5
INDData AnalystB12.5
USSoftware EngineerB25
INDSoftware EngineerC


USData AnalystA20



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)


Here is a simple formula solution:


In E2:



You can try the attached file with Power Query.

Hi @SatishBadiger 

For DAX (Power Pivot)


Average Rate measure:

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