topic Re: Calculate sumx to recognize blanks in DAX Commands and Tips

Calculate sumx to recognize blanks

petercd — Fri, 18 Oct 2024 19:55:17 GMT

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

Re: Calculate sumx to recognize blanks

LuizKoller — Fri, 18 Oct 2024 20:29:55 GMT

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

Re: Calculate sumx to recognize blanks

xifeng_L — Sun, 20 Oct 2024 16:06:03 GMT

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~

Re: Calculate sumx to recognize blanks

Anonymous — Mon, 21 Oct 2024 01:35:33 GMT

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

Re: Calculate sumx to recognize blanks

petercd — Thu, 31 Oct 2024 11:58:11 GMT

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)