topic Re: SUM if second column is 0 in DAX Commands and Tips

SUM if second column is 0

joshua1990 — Thu, 14 Jul 2022 11:54:34 GMT

Hi experts!

I have a table/ Matrix that shows me 2 value columns per article

Article	Value 1	Value 2	Final Value
A	50	250	0
B	0	52	52
C	0	63	63
D	50	0	0

Now I would like to create a measure "Final Value" that displays a value if Value 1 is 0.

But how? I need probably a SUMX, right?

Re: SUM if second column is 0

technolog — Thu, 14 Jul 2022 12:37:58 GMT

Do you mean measure or calculated column?

You can create calculated column:

calc col = IF([Value 1] = 0, "Value 1 equal zero", BLANK())

Or you can try to use measure:

measure = IF(SUM([Value 1]) = 0, "Value 1 equal zero", BLANK())

Re: SUM if second column is 0

joshua1990 — Thu, 14 Jul 2022 13:40:25 GMT

technolog : Thanks, I want a measure in the end. Your approach is not working. I want a value in the end with the correct Grand and Sub Total. I would assume I need a SUMX

Re: SUM if second column is 0

technolog — Thu, 14 Jul 2022 14:02:40 GMT

Could you show more clearly the result and how you want to use measure?

Re: SUM if second column is 0

joshua1990 — Thu, 14 Jul 2022 14:27:04 GMT

Article	Value 1	Value 2	Final Value
A	50	250	0
B	0	52	52
C	0	63	63
D	50	0	0
Total	100	365	115

Please see Final Value as expected result technolog

Re: SUM if second column is 0

tamerj1 — Thu, 14 Jul 2022 16:59:20 GMT

Hi joshua1990
Not sure if this is what you're looking for. However please refer to below method using one measure and utilizing the column subtotals

Values = VAR CurrentValue = SUM ( Sheet1[Value] ) VAR Value1 = CALCULATE ( SUM ( Sheet1[Value] ), Sheet1[Attribute] = "Value 1" ) VAR Result = IF ( HASONEVALUE ( Sheet1[Attribute] ), CurrentValue, IF ( Value1 = 0, CurrentValue, 0 ) ) RETURN Result

Re: SUM if second column is 0

v-jianboli-msft — Tue, 19 Jul 2022 10:05:23 GMT

Hi joshua1990 ,

Please try:

Final Value = SUMX('Table',IF([Value 1]=0,[Value 2],0))

Output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: SUM if second column is 0

v-jianboli-msft — Thu, 04 Aug 2022 02:12:04 GMT

Hi joshua1990 ,

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or if you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

Refer to:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.