Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
I have an employee directory that lists every employee in the company, along with the office they are assigned to. I need to provide a measure that tells us what percentage of our offices have 10 or more employees, but this is proving to be much more complicated than I anticipated.
Trying to identify how many offices have 10 or more employees didn't work because I can't figure out how to count employees by office:
So I realized I probably need a summarized table where it's giving me each office and the employee count, but am getting consistent errors every time I try to use the GROUPBY or SUMMARIZE function:
returns the error "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value". I've tried changing that last COUNT expression to simply using the measure "# Active Employees", and I get the same error.
I also tried a GROUPBY:
We only have 14 offices, 6 of which have 10 or more employees, so this isn't giant complex data.
What am I missing here? Thank you!
Solved! Go to Solution.
The SUMMARIZE function returns a table, but measures must return scalar values. That's why you get the error.
Here's a measure to get the number of offices with more than 10 employees
Offices >10 Employees =
VAR _SelectedNumEmployees = 10
VAR _Result =
COUNTROWS(
FILTER(
VALUES('Employee Directory'[Office - Assigned]),
CALCULATE(COUNT('Employee Directory'[Employee ID])) > _SelectedNumEmployees
)
)
RETURN
_Result
You could replace CALCULATE(COUNT('Employee Directory'[Employee ID])) in the above with a measure that counts number of employees.
@PaulOlding Aha, that worked, thank you so much!! I guess I need to learn more about the VAR function.
@Anonymous Are you creating a measure or a calculated column. You most likely want a measure.
@Greg_Deckler I've tried both, get the same error each time:
This is when I tried it as a column. Same error. I don't understand why it thinks I'm referencing multiple columns.
The SUMMARIZE function returns a table, but measures must return scalar values. That's why you get the error.
Here's a measure to get the number of offices with more than 10 employees
Offices >10 Employees =
VAR _SelectedNumEmployees = 10
VAR _Result =
COUNTROWS(
FILTER(
VALUES('Employee Directory'[Office - Assigned]),
CALCULATE(COUNT('Employee Directory'[Employee ID])) > _SelectedNumEmployees
)
)
RETURN
_Result
You could replace CALCULATE(COUNT('Employee Directory'[Employee ID])) in the above with a measure that counts number of employees.
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
24 | |
9 | |
7 | |
6 | |
6 |
User | Count |
---|---|
29 | |
11 | |
11 | |
10 | |
6 |