Forum Discussion

petercd's avatar
petercd
Frequent Visitor
2 years ago
Solved

Calculate sumx to recognize blanks

How can I force sumx to recognize blanks? If Pres Amt is blank, I would like Pres Amt to be equal to 0. Var $ column is caculating the rows correctly, but not the total so I used sumx. Churn is corre...
  • petercd's avatar
    petercd
    1 year ago

    Below is my solution without sharing sensitive data. I created this calculation by division. 

    Churn_DIV =
        VAR _ValueFilter =
            FILTER(
                KEEPFILTERS(
                    SUMMARIZECOLUMNS(
                        'Table'[Division],
                        'Table'[Customer Code],
                        "Var__2", 'All Measures'[Var $],
                        "Revenue Type", IGNORE('All Measures'[Revenue Type])
                    )
                ),
                [Revenue Type] = "Churn"
            )

        RETURN
            CALCULATE('All Measures'[Var $], _ValueFilter)

     

LuizKoller's avatar
LuizKoller
Icon for Resolver I rankResolver I
2 years ago

if you wanna avoid showing Churn for positive numbers in Pres Amt, try using COALESCE

 

Churn =
 SUMX(
    SUMMARIZE(Table1,Table1[Customer]),
       IF(COALESCE([PY Pres Amt], 0)  >= 0 && COALESCE([Pres Amt], 0)  <= 0, [Var $]))