Forum Discussion

petercd's avatar
petercd
Frequent Visitor
1 year 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 correct, except for the blanks

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



 

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

     

4 Replies

  • LuizKoller's avatar
    LuizKoller
    Icon for Resolver I rankResolver I
    1 year 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 $]))
  • xifeng_L's avatar
    xifeng_L
    Icon for Super User rankSuper User
    1 year ago

    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~

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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
  • petercd's avatar
    petercd
    Frequent Visitor
    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)