PL-300 · Question #381
You have a Power BI model that contains a table named Employees. The table contains the following columns: - Employee ID - First Name - Last Name - Department - Salary Each employee is uniquely…
The correct answer is C. CALCULATE(AVERAGE('Employees'[Salary]), 'Employees'[Department] = "Sales"). To calculate the average salary for a specific department, the DAX CALCULATE function is used to apply a filter context to the AVERAGE function.
Question
You have a Power BI model that contains a table named Employees. The table contains the following columns:
- Employee ID
- First Name
- Last Name
- Department
- Salary
Each employee is uniquely identified by using Employee ID. You need to create a DAX measure that will calculate the average salary of all the employees in the sales department. Which DAX expression should you use?
Options
- ADISTINCTCOUNT('Employees'[Salary])
- BCALCULATE(DISTINCTCOUNT('Employees'[Salary]), 'Employees'[Department] = "Sales")
- CCALCULATE(AVERAGE('Employees'[Salary]), 'Employees'[Department] = "Sales")
- DAVERAGE('Employees'[Salary])
How the community answered
(45 responses)- A2% (1)
- B4% (2)
- C80% (36)
- D13% (6)
Why each option
To calculate the average salary for a specific department, the DAX CALCULATE function is used to apply a filter context to the AVERAGE function.
`DISTINCTCOUNT('Employees'[Salary])` would count the number of unique salary values, not calculate their average, and it does not filter by department.
`CALCULATE(DISTINCTCOUNT('Employees'[Salary]), 'Employees'[Department] = "Sales")` would count the distinct salaries within the sales department, which is not the average salary.
The `CALCULATE` function applies a filter to the `AVERAGE('Employees'[Salary])` expression, ensuring that the average is computed only for rows where `Employees[Department]` is equal to 'Sales', thereby calculating the average salary exclusively for the sales department.
`AVERAGE('Employees'[Salary])` would calculate the average salary for *all* employees in the table, ignoring the departmental filter requirement.
Concept tested: DAX CALCULATE function with filters
Source: https://learn.microsoft.com/en-us/dax/calculate-function-dax
Community Discussion
No community discussion yet for this question.