cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Need average to include 0s

I have a measure that calculates voluntary turnover and if it is blank, it returns 0. When I try to visualize turnover by region and company in a matrix (Region and Company at the row level), it does not include these 0s in the average. For example:
REGION 1                 12.50%
Company A              12.50%
Company B                   0%
Company C                   0%
Company D                   0%
Company E                    0%

REGION 1 shows average of 12.50% when it should be 2.5%. How do I get this measure to include the zeros when visualizing the average in a matrix?

1 ACCEPTED SOLUTION
Community Support

For your question, here is the method I provided:

Measure =
var _CountCompany =
CALCULATE(
COUNTROWS('Table'),
FILTER(
ALL('Table'),
'Table'[Region] = MAX('Table'[Region])
)
)
var _TotalVoluntaryTurnover =
SUMX(
FILTER(
ALL('Table'),
'Table'[Region] = MAX('Table'[Region])
),
'Table'[voluntary turnover]
)
RETURN
IF(
'Table'[voluntary turnover] <> 0,
_TotalVoluntaryTurnover / _CountCompany,
0
)

Create a matrix.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 REPLIES 2
Community Support

For your question, here is the method I provided:

Measure =
var _CountCompany =
CALCULATE(
COUNTROWS('Table'),
FILTER(
ALL('Table'),
'Table'[Region] = MAX('Table'[Region])
)
)
var _TotalVoluntaryTurnover =
SUMX(
FILTER(
ALL('Table'),
'Table'[Region] = MAX('Table'[Region])
),
'Table'[voluntary turnover]
)
RETURN
IF(
'Table'[voluntary turnover] <> 0,
_TotalVoluntaryTurnover / _CountCompany,
0
)

Create a matrix.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Super User
Average Voluntary Turnover by Region =
AVERAGEX(
SUMMARIZE(
YourTableName,
YourTableName[Region],
"VoluntaryTurnover", [Voluntary Turnover]
),
[VoluntaryTurnover]
)