SOLVED

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

12

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

12

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)
Solution

@SatishBadiger 

Here is a simple formula solution:

S1527.png

In E2:

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

@SatishBadiger 

You can try the attached file with Power Query.

Hi @SatishBadiger 

For DAX (Power Pivot)

_Screenshot.png

Average Rate measure:

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