Averaging Sales department salaries with CALCULATE and a department filter

PL-300 By islever Editorial Last updated

Create model calculations by using DAX

Short answer

C CALCULATE with AVERAGE and a Department = Sales filter returns the sales department's mean salary.

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?
  1. DISTINCTCOUNT(‘Employees’[Salary])
  2. CALCULATE(DISTINCTCOUNT(‘Employees’[Salary]), ‘Employees’[Department] = “Sales”)
  3. CALCULATE(AVERAGE(‘Employees’[Salary]), ‘Employees’[Department] = “Sales”) Correct Answer
  4. AVERAGE(‘Employees’[Salary])

Key Takeaways

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.

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

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

This link opens the official vendor documentation in a new tab.

Community Votes

C
100%

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

Discussion 6 contributions

Compiled from public community threads and reader submissions, lightly edited for clarity. Handles and vote tallies are shown as recorded, and comments reflect the views of individual contributors.

From public community threads 6

  1. jaume 👍 3 Answered 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
  2. Dani_eL 👍 3 Answered C
    C. CALCULATE(AVERAGE(‘Employees’[Salary]), ‘Employees’[Department] = “Sales”)
  3. Seda_ 👍 4
    C is the correct answer.
  4. MANANDAVEY 👍 4 Answered C
    C is right
  5. Florinuca 👍 1
    Yes, the correct answer is C.
Show 1 more comment
  1. Palwashai 👍 1
    C is correct

Reader comments & corrections

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

Log in to comment, report an error, or add a note about this question.

PREVIOUSYEAR returns the full previous year, not the same day of last year

S-grade

Shrinking a Power BI model for monthly transaction totals with a star schema

S-grade

Northwind Traders semantic model: choosing Import storage mode for the Power BI tables

S-grade

Connecting Power BI Desktop to Azure DevOps Analytics views with the OData Feed connector

S-grade

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