Forum Discussion

swaroopkumarmg's avatar
swaroopkumarmg
Icon for Helper II rankHelper II
7 years ago
Solved

SUMIFS in DAX

Hello there, I'm trying to replicate a function- SUMIFS from excel to Power BI.    I have the following Tables and i need to sum the Hours (Total Hours) based on the criterias.   Table 1: St...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    If you want the calculated column to be in Table 1, the tables need to be connected by a relationship. I would recommend you create a unique key for each stage and band combination so you can connect the tables.

    Use the following as a calculated column in both tables.

    StageBandKey =
CONCATENATE ( Table1[Stage Code], Table1[Band] )

    StageBandKey =
    CONCATENATE ( Table2[Stage Code], Table2[Band] )

    Then connect the tables via the relationship view using the shared key.

    You'll then be able to use the previous code in a calculated column in Table 1 to get the hours for each stage - band combination.

v-eachen-msft's avatar
v-eachen-msft
Icon for Community Support rankCommunity Support
7 years ago

Hi swaroopkumarmg ,

 

You can create the measure “Total Hours” to meet your needs.

Total Hours =
VAR s =
    CALCULATE (
        SUM ( Table2[Hours] ),
        FILTER (
            Table2,
            Table2[Band] = MAX ( Table1[Band] )
                && Table2[Stage Code] = MAX ( Table1[Stage Code] )
        )
    )
RETURN
    IF ( ISBLANK ( s ), 0, s )

Then you can get result you want.

Best Regards,

Eads

 

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