Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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.
Try the following as a calculated column in Table 2:
Total hours column = CALCULATE ( SUM ( Table2[Hours] ), Table2[Band], Table2[Stage Code] )
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?
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.
That worked like a charm.
thanks a ton!! 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |