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.

     

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.