Sum with multiple conditions (sumifs in excel)

[arp2]

Dear All,

I am trying to achieve the results as shown here below.....I tried multiple methods using similar old posts but struggled.....Plz help me out.

Table Sample

Employee ID	Project Number	Hours	Week	Discipline	Criteria
A100	P1	10	Wk-1	Civil	Sum-1
A101	P1	6	Wk-1	Civil	Sum-1
A102	P2	5	Wk-1	Civil	Sum-1
A103	P3	6	Wk-1	Civil	Sum-1
A104	P4	8	Wk-1	PM	Sum-2
A105	P2	10	Wk-1	PE	Sum-1
A100	P5	9	Wk-1	Civil	Sum-2
A101	P5	5	Wk-1	Civil	Sum-2
A102	P6	9	Wk-1	Civil	Sum-2
A103	P5	5	Wk-1	Civil	Sum-2
A104	P7	8	Wk-1	PM	Sum-2
A105	P8	7	Wk-1	PE	Sum-1
A100	P1	8	Wk-2	Civil	Sum-1
A101	P1	8	Wk-2	Civil	Sum-1
A102	P2	9	Wk-2	Civil	Sum-1
A103	P3	10	Wk-2	Civil	Sum-1
A104	P4	10	Wk-2	PM	Sum-2
A105	P2	5	Wk-2	PE	Sum-1
A100	P5	7	Wk-2	Civil	Sum-2
A101	P5	5	Wk-2	Civil	Sum-2
A102	P6	6	Wk-2	Civil	Sum-2
A103	P5	8	Wk-2	Civil	Sum-2
A104	P7	8	Wk-2	PM	Sum-2
A105	P8	6	Wk-2	PE	Sum-1

Required Result-1 (hours booked only on Project P1, P2, P3 and P8)

Discipline	Wk-1	Wk-2
Civil	27	35
PM	0	0
PE	17	11

Required Result-2 (hours booked on Projects except P1, P2, P3 and P8)

Discipline	Wk-1	Wk-2
Civil	28	26
PM	16	18
PE	0	0

In excel I'm achieving the result as follows:

[Anonymous · Accepted solution]

Hi @arp2 ,

I have created a simple sample, please refer to it to see if it helps you.

Create 2 measures.

sum_1 =
CALCULATE (
    SUM ( 'Table'[Hours] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Criteria] = "Sum-1"
            && 'Table'[Discipline] = SELECTEDVALUE ( 'Table'[Discipline] )
            && 'Table'[Week] = SELECTEDVALUE ( 'Table'[Week] )
    )
)

sum_2 =
CALCULATE (
    SUM ( 'Table'[Hours] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Criteria] = "Sum-2"
            && 'Table'[Discipline] = SELECTEDVALUE ( 'Table'[Discipline] )
            && 'Table'[Week] = SELECTEDVALUE ( 'Table'[Week] )
    )
)

I have also created 2 columns, please refer to my pbix file.

Best Regards
Community Support Team _ Polly

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

[arp2]

Thank you so much

[amitchandak]

@arp2 , for except you have to use independent table

Need of an Independent Table in Power BI - Exclude: https://youtu.be/lOEW-YUrAbE