Calculate sumx to recognize blanks

[petercd]

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 correct, except for the blanks

Churn =

 SUMX(

    SUMMARIZE(Table1,Table1[Customer]),

       IF([PY Pres Amt]  >= 0 && [Pres Amt]  <= 0, [Var $]))

[petercd · Accepted solution]

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)

[Anonymous]

Hi @petercd 

Please try this formula:

Churn =

 SUMX(

    SUMMARIZE(Table1,Table1[Customer]),

       IF([PY Pres Amt]  >= 0 && [Pres Amt]  <= 0, [Var $])) + 0

Best Regards,

Bof

[xifeng_L]

Hi @petercd ,

A blank value is automatically converted to 0 when it encounters a comparison operator, so your Churn expression above shouldn't be blank in the first two rows. For exmaple, In below demo, the Churn correctly return the value in first two rows.

Since it's not clear what the exact calculation environment is in your report, I can only suggest that you use the following measure to see if you can meet the requirements:

Churn = 
SUMX(
    CROSSJOIN(
        VALUES('Table'[DimField1),  // Dimension fields used for matrix row labels
        VALUES('Table'[DimField2),  
        ...
    ),
    [Var $]
)

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~

[LuizKoller]

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 $]))