topic SUM value is Row in a Column Equals a Specific Value and Value is Greater than Zero in DAX Commands and Tips

SUM value is Row in a Column Equals a Specific Value and Value is Greater than Zero

MS3 — Fri, 21 Feb 2020 13:01:50 GMT

Hello!

I am trying to SUM Values [Amount] if a Column [FS Item (Account)] is Equal to a Specific Value [173] and the [Amount] is Positive / Greater than Zero.

The formula I have to SUM the values is

CALCULATE (

SUM ( Masterdata[Amount] ),

Masterdata[FS Item (Account)]="173"

)

which provides the SUM, but I am unsure how to add the condition for it to add only the [Amount] that are greater than zero.

Would appreciate any help.

Best regards,

Re: SUM value is Row in a Column Equals a Specific Value and Value is Greater than Zero

Anonymous — Fri, 21 Feb 2020 13:06:32 GMT

it should be like this
CALCULATE (

SUM ( Masterdata[Amount] ),

Masterdata[FS Item (Account)]="173"
&& [Amount] >0

)

Re: SUM value is Row in a Column Equals a Specific Value and Value is Greater than Zero

MS3 — Fri, 21 Feb 2020 13:23:36 GMT

Needed to add the table for Amount as per below:

CALCULATE ( SUM ( Masterdata[Amount] ),
Masterdata[FS Item (Account)]="173"
&& Masterdata [Amount] >0
)

When I use the expression it tells me

"The expression contains multiple columns, but only a single column can be used in a True / False expression that is used as a Table Filter Expression"

Indeed [Amount] and [FS Item (Account)]] are columns in my Masterdata table.

Re: SUM value is Row in a Column Equals a Specific Value and Value is Greater than Zero

Anonymous — Fri, 21 Feb 2020 13:28:24 GMT

sry, my bad
do like this with a filter function :

Re: SUM value is Row in a Column Equals a Specific Value and Value is Greater than Zero

Pragati11 — Fri, 21 Feb 2020 13:28:31 GMT

Hi MS3 ,

Try the following DAX:

CALCULATE (

SUM ( Masterdata[Amount] ),

FILTER(

Masterdata, Masterdata[FS Item (Account)]="173" && Masterdata[Amount] > 0

)

Kindly give Kudos if the solution is helpful and Mark it as a solution if it resolves the issue! 🙂

Thanks,

Pragati

Re: SUM value is Row in a Column Equals a Specific Value and Value is Greater than Zero

MS3 — Fri, 21 Feb 2020 13:43:38 GMT

Thank you very much!!!

It worked wonderfully!