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 those missing values in the denominator. How can I do that? 

 

Thanks!

 

Lai

 

Percentage =
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]))
)

  • 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] <> "."
    )
)

3 Replies

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    hello, 

     

    this solution of removing missing in calculate function (dax) doesn't seem to work for me. Perhap because the calculate is based on other measures which already have filter in them. I ended up just using simple If(isblank(variable), blank(), calculate(variable - variable2).