Sum if Formula

[gmasta1129]

Hello,

I have the following columns in my report:

Cust No.

Total Amount

Is Credit Open

I need to produce the following formula in Power BI desktop...

If(Is Credit Open = "Y",SUMIF(total Amount by Cust. No).  

Can someone please help? 

Thank you! 

[gmasta1129]

Thank you for the quick response but unfortunately neither of the formulas worked.. 

Please see below highlighted column for result I am looking for.  I want to sum the total amount by cust. no and only the rows that contain a Y in Is Credit Open

[Thennarasu_R]

Measure=
               Calculate(Total Amount,ISCredit="Y")

[v-xiaosun-msft]

Hi @gmasta1129 ,

According to your description, here is my solution.

You can try to implement power bi sumif with the function in dax that is “Calculate”.

I created a sample.

For example, if we want filter "Is Credit Open"="Y" && "Cust. No">2, we can do as followings:

Measure =
IF (
    MAX ( 'Table'[Is Credit Open] ) = "Y",
    CALCULATE ( SUM ( 'Table'[Total Amount] ), 'Table'[Cust No.] > 2 )
)

Final output:

You can set the filter section according to your needs.

I attach my sample below for your reference.

Best Regards,
Community Support Team _ xiaosun

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

[VahidDM]

Hi @gmasta1129 

You need a measure, but how do you want to use that measure in your report? Do you want to use that in a table that has "Cust No."? if yes, try this:

Measure =
CALCULATE (
    SUM ( Table[Total Amount] ),
    KEEPFILTERS ( Table[Credit Open] = "Y" )
)

On the other hand, You can add Cust. No and total Amount to your visual and then use Credit Open in the filter pane and set that to show the Y values only.

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

Appreciate your Kudos!! 

LinkedIn | Twitter | Blog | YouTube