COUNT
Counts the cells in a column that hold a number.
COUNT(<column>)Returns: A whole number (a scalar). Zero when no numbers are found.
What it does
COUNT tallies the cells in one column that contain a number. Text, blanks and logical values are not counted. A zero is a number, so it is counted. Use COUNT when you want to know how many entries in a numeric column carry an amount, such as how many lines have a return amount recorded. For a count of rows, use COUNTROWS, and for a count of filled cells of any type, use COUNTA.
Syntax and arguments
- column: a column reference such as
Sales[ReturnAmount]. Only one column is accepted.
Example
The retail controller wants the number of sales lines that carry a return amount, to measure how often returns are recorded.
Lines With Return Amount = COUNT(Sales[ReturnAmount])
Lines with a blank return amount are skipped. Lines where the return amount is recorded as 0 are counted, because 0 is a number. If the business expects zeros to mean "no return," this measure overstates the returns, so the next example fixes that.
Example: a second, different use
Count only the lines where a return really happened:
Lines Returned =
CALCULATE(
COUNTROWS(Sales),
Sales[ReturnAmount] > 0
)
COUNTROWS counts the lines that pass the filter, and the filter keeps only the lines with a positive return. This is clearer than counting numbers that might be zeros.
Things to watch
- COUNT ignores text, so a column of amounts stored as text will show a count of zero. Fix the data type first.
- Zeros are counted. This is the most common surprise, and the reason COUNTROWS with a filter is often the better choice.
- COUNT takes a column. It does not count rows of a table. Use COUNTROWS or COUNTX for that.
- COUNT is for numeric values. For names, codes or any other text, use COUNTA.
- A count of numbers is not a count of distinct items. A customer with ten invoices adds ten, not one. Use DISTINCTCOUNT for distinct customers.
In business terms
COUNT is used to test data completeness: how many invoices have an amount, how many employees have a salary recorded, how many bank lines carry a value. A low count is often the first sign of a missing feed.