SUMIF in powerBI?

[Anonymous]

Hi All,

I have used =SUMIF(A:A,A2,X:X) in excel and I am trying to implement the same in powerBI. When I do implement, the solution does not seem to match.

I am new to powerbi, so any help would be appreciated.

Thank you!

[selimovd]

Hey @Anonymous ,

you can get a similar result with CALCULATE. It's a little challenging as you didn't give any details. But for example this could be one approach:

Sumif =
CALCULATE(
    SUM( myTable[Sum Column] ),
    myTable[X] = "X"
)

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Anonymous]

Hi @selimovd ,

Thank you for your response!

I used the same formula. But, the value "X" is not constant. 

In excel, when used this formula =SUMIF(A:A,A2,X:X) and when dragged, the cell reference changes. For eg:=SUMIF(A:A,A3,X:X), =SUMIF(A:A,A4,X:X) and so on.

Can I please know how to implement that?

Thank you!

[selimovd]

@Anonymous And where is the value of "X" coming from in Power BI?

[Anonymous]

Hi @selimovd ,

The excel looks like the image attached. PowerBI have the exact same columns and looks the same way. The formula =SUMIF(A:A,A2,X:X) is applied in the Y column in excel. How could I implement the same way in PowerBI?

Thank you!

[selimovd]

Hey @Anonymous ,

then the following calculated column should do it:

Sumif =
VAR vRowPartPlant = myTable[PartPlant]
RETURN
    CALCULATE(
        SUM( myTable[VarianceValue] ),
        myTable[PartPlant] = vRowPartPlant
    )

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Anonymous]

Hi @selimovd ,

Thank you so much!

I applied the formula, I get the same value as the column variance value.

But I need the sum. I have attached the image below:

In the above image, I get the sum of variance value for that partplant. Is that possible in DAX?

Thanks again!

[selimovd]

Hey @Anonymous ,

can you maybe post a screenshot from Power BI?

The screenshots from Excel won't help at that point.

Thank you and best regards

Denis

[Anonymous]

Hi @selimovd ,

Sure. I have attached the image below.

Thank you!

[selimovd · Accepted solution]

Hey @Anonymous ,

OK, that makes sense. In this case we have to add an ALLEXCEPT.

Try the following measure:

Variance in Week =
VAR vRowPartPlant = 'Order Non-Compliance'[PartPlant]
RETURN
    CALCULATE(
        SUM( 'Order Non-Compliance'[VarianceValue] ),
        'Order Non-Compliance'[PartPlant] = vRowPartPlant,
        ALLEXCEPT(
            'Order Non-Compliance',
            'Order Non-Compliance'[PartPlant]
        )
    )

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Anonymous]

Thanks a lot! @selimovd 

[selimovd]

@Anonymous  I'm happy it's working now 🙂