Forum Discussion

finley0726's avatar
finley0726
Frequent Visitor
2 years ago
Solved

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?

 

 

  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
    aduguid
    Memorable Member
    2 years ago
    Average Voluntary Turnover by Region = 
    AVERAGEX(
        SUMMARIZE(
            YourTableName, 
            YourTableName[Region], 
            "VoluntaryTurnover", [Voluntary Turnover]
        ), 
        [VoluntaryTurnover]
    )