Forum Discussion
Helper IISUMIFS 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:
| 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
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.
5 Replies
swaroopkumarmgThanks 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?
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
Community Support
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.
