SUM if second column is 0

[joshua1990]

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?

[v-jianboli-msft]

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.

[v-jianboli-msft]

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.

[tamerj1]

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

[joshua1990]

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 

[technolog]

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

[joshua1990]

@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

[technolog]

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