Forum Discussion

weilai0521's avatar
weilai0521
Frequent Visitor
7 years ago
Solved

Exclude missing values with calculate

Hi,   I have a formula like this to calculate percentages which can be changing dynamically with age, gender filters. But there are some 'DATA'[XXX] with missing values "." and I want to exclude th...
  • Stachu's avatar
    Stachu
    7 years ago

    add the 

    DATA[XXX]<>"."

    to your denominator calculate, like this:

    DIVIDE (
    CALCULATE (
        SUM ( 'DATA'[Weight] ),
        FILTER (
            'DATA',
            'DATA'[XXX] = "Yes"
                && 'DATA'[Age] = [Selected_Age]
                && 'DATA'[Gender] = [Selected_Gender]
        )
    ),
    CALCULATE (
        SUM ( 'DATA'[Weight] ),
        ALLEXCEPT ( 'DATA', 'DATA'[Year], 'DATA'[Age], 'DATA'[Gender] ),
        DATA[XXX] <> "."
    )
)
  • weilai0521's avatar
    weilai0521
    7 years ago

    Thank you! This is very helpful!

Stachu's avatar
Stachu
Community Champion
7 years ago

add the 

DATA[XXX]<>"."

to your denominator calculate, like this:

DIVIDE (
    CALCULATE (
        SUM ( 'DATA'[Weight] ),
        FILTER (
            'DATA',
            'DATA'[XXX] = "Yes"
                && 'DATA'[Age] = [Selected_Age]
                && 'DATA'[Gender] = [Selected_Gender]
        )
    ),
    CALCULATE (
        SUM ( 'DATA'[Weight] ),
        ALLEXCEPT ( 'DATA', 'DATA'[Year], 'DATA'[Age], 'DATA'[Gender] ),
        DATA[XXX] <> "."
    )
)
weilai0521's avatar
weilai0521
Frequent Visitor
7 years ago

Thank you! This is very helpful!