Averaging Sales department salaries with CALCULATE and a department filter

Create model calculations by using DAX

Answer Correct answer: C — CALCULATE with AVERAGE and a Department = Sales filter returns the sales department's mean salary.

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?

DISTINCTCOUNT(‘Employees’[Salary])
CALCULATE(DISTINCTCOUNT(‘Employees’[Salary]), ‘Employees’[Department] = “Sales”)
CALCULATE(AVERAGE(‘Employees’[Salary]), ‘Employees’[Department] = “Sales”) Correct Answer
AVERAGE(‘Employees’[Salary])

calculate average dax filter context department salary measure

Community Votes

100%

100% of anonymous learners picked answer C. Votes are pick records left by other test-takers — they are not the verified answer.

Community Insight

The pattern is CALCULATE over an aggregate with a filter argument — CALCULATE(AVERAGE(Salary), Department = "Sales") swaps the filter context to the sales department before averaging.

An Employees table lists staff by ID, name, department, and salary, and a measure must return the average salary of employees in the sales department only.

Using DISTINCTCOUNT to answer an average question — counting distinct salaries measures salary variety, not the mean pay of the department's employees.

Community Discussion (8 comments)

jaume 👍 3 Selected: C

DISTINCTCOUNT as in Option A is definitively not calculating any averge but just how many different salaries there are in the table Similar, option B is calculation how many different salaries there are in the "Sales" Deptm Option D is calculating the total salary average in the company not just for the "Sales" department (and it could be affected by CONTEXT filters) In option C we are calculating the AVERAGE of the Salary, just for employees in SALES department and encapsulating calculation in CALCULATE to remove any report filter

Seda_ 👍 4

C is the correct answer.

shahrzadkhb 👍 3 Selected: C

C is the correct answer.

Palwashai 👍 1

C is correct

MANANDAVEY 👍 4 Selected: C

C is right

Dani_eL 👍 3 Selected: C

C. CALCULATE(AVERAGE(‘Employees’[Salary]), ‘Employees’[Department] = “Sales”)

Florinuca 👍 1

Yes, the correct answer is C.

INDEAVR 👍 3 Selected: C

C. CALCULATE(AVERAGE(‘Employees’[Salary]), ‘Employees’[Department] = “Sales”)

Comments & Corrections

No comments yet — spotted an error or have a note? Share it below.

Expert Analysis

Why the Answer Is Correct

CALCULATE evaluates its first argument in a modified filter context: the boolean filter Employees[Department] = "Sales" restricts the table to sales-department rows, and AVERAGE(Employees[Salary]) then computes their mean salary. This is exactly the requested measure — the department-scoped average — expressed in the standard DAX filter pattern.

Why the Other Options Are Wrong

DISTINCTCOUNT(Employees[Salary]) (A) counts how many different salary values exist across all departments, with no department filter and no averaging. Option B counts distinct salary values within the sales department — better scoped, but still a count, not an average. AVERAGE(Employees[Salary]) (D) averages across every department in the model, ignoring the sales-only requirement.

Community Comment Notes

A commenter notes DISTINCTCOUNT in options A and B merely counts how many different salaries exist, never an average, and option D ignores the department restriction. The unanimous vote lands on C.

Official Reference

https://learn.microsoft.com/en-us/dax/calculate-function-dax

Related Analysis

Practice All PL-300 Questions

Access 116 questions with complete answers and detailed explanations.

View Full PL-300 Practice Test →

← Back to PL-300 Study Guide