Need average to include 0s

[finley0726]

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?

[Anonymous · Accepted solution]

Hi @finley0726 

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.

[aduguid]

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