SUMIFS in DAX

[swaroopkumarmg]

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:

Stage Code	Stage	Band	Total Hours
1	Suspect	250	50
1	Suspect	500	0
2	In progress	250	0
2	In progress	500	25
3	Won	250	28
3	Won	500	23

Table 2:

Case	Hours	Stage Code	Band
AAAA	20	1	250
BBBB	25	2	500
CCCC	23	3	500
DDDD	30	1	250
EEEEE	28	3	250

Formula used: =SUMIFS(Table2[Hours],Table2[Band],[Band],Table2[Stage Code],[Stage Code])

Please help me figure this out!!

Thanks

SMG

[v-eachen-msft]

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.

[Anonymous]

Try the following as a calculated column in Table 2:

Total hours column =
CALCULATE ( SUM ( Table2[Hours] ), Table2[Band], Table2[Stage Code] )

[swaroopkumarmg]

Thanks a ton for your reply and suggestion.

I tried this, it did not give any error, but the Hours Column in the Table 1 is with blank values

Any suggestion?

[Anonymous · Accepted solution]

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.

[swaroopkumarmg]

That worked like a charm. 

thanks a ton!! 🙂