Problem Statement:
The HR department wants to calculate the average score of employee feedback in a survey. The feedback column includes numeric ratings (from 1 to 5), but some employees left this field blank, while others entered text comments instead of numbers. To get a realistic average score, blanks should be ignored, but text values should be treated as 0 to reflect the lack of numeric input.
Why AVERAGEA is the Solution:
- In this scenario, AVERAGEA {Syntax: AVERAGEA(<column>) can handle row-wise calculations like:
- It calculates the average by treating numeric values as they are, text as 0, and ignores blanks
- It provides a more realistic average in situations where non-numeric entries should count as 0 rather than be excluded entirely.
DAX Example:
Assuming a column FeedbackScore that contains a mix of numbers, text, and blanks:
DAX:
Use Case: Average_Score = AVERAGEA(Survey[FeedbackScore])
This will:
- Include each numeric feedback score as given,
- Count text entries as 0,
- Ignore blanks.
Key Point:
AVERAGEA is ideal when you want to include non-numeric entries in the average calculation (counting them as 0) and ensure a more accurate overall reflection of the data, particularly useful in survey or feedback analysis.