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...
  • 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.

     

aduguid's avatar
aduguid
Icon for Memorable Member rankMemorable Member
2 years ago
Average Voluntary Turnover by Region = 
    AVERAGEX(
        SUMMARIZE(
            YourTableName, 
            YourTableName[Region], 
            "VoluntaryTurnover", [Voluntary Turnover]
        ), 
        [VoluntaryTurnover]
    )