Jun 29 2022 09:03 AM
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.
Jun 29 2022 09:08 AM
SolutionHere is a simple formula solution:
In E2:
=AVERAGEIFS($D$2:$D$6,$A$2:$A$6,A2,$B$2:$B$6,B2)
Jun 29 2022 10:18 AM
You can try the attached file with Power Query.
Jun 29 2022 11:00 AM - edited Jun 29 2022 10:18 PM
For DAX (Power Pivot)
Average Rate measure:
=CALCULATE ( AVERAGE ( Table1[Rate / Hour] ), ALL ( Table1[Department] ) )
Jun 29 2022 09:08 AM
SolutionHere is a simple formula solution:
In E2:
=AVERAGEIFS($D$2:$D$6,$A$2:$A$6,A2,$B$2:$B$6,B2)