Mimic Excel SUMIFS functionality to create a pipeline table (phases, sectors, actuals & targets)

Question

Hi,&nbsp;I'm struggling to recreate some functionality that I have in Excel. Any help would be greatly appreciated.&nbsp;I'm creating a monthly pipeline report that shows how opportunities are progressing through a sales process. So far I have successfully manipulated my source data in Power BI to the point where I can get almost all the information and graphs that I need.&nbsp;The only thing remaining is to create a table that summarises the value of projects, split by sector, that have passed through each phase in a given month. And also the target values.&nbsp;------The current format I have got to, is a list of opportunities which have the following info:&nbsp;Opportunity IDTarget or actual?ValueSectorPhase 1Phase 2Phase 3Opp-001Actual£101.00SEC-AYESYESYESOpp-002Actual£102.00SEC-B&nbsp;YES&nbsp;Opp-003Actual£103.00SEC-B&nbsp;&nbsp;&nbsp;...and so on.&nbsp;---The output that I desire is a table that shows the value that has passed through each phase this month, split into sectors. Anbd also the targets for each phase and sector. (The target data is already in the same input table).Like this:&nbsp;PhaseTarget or actual?SEC-ASEC-BSEC-CSEC-DPhase 1Target1000110012001300Phase 1Actual547104230118Phase 2Target2000210022002300Phase 2Actual316326343110Phase 3Target3000310032003300Phase 3Actual1010224107&nbsp;As you can see in the linked Excel file, I have created the exact output that I require using a SUMIFS with 3 sets of criteria.&nbsp;I would like to remove this manual step using Excel&nbsp; and would greatly appreciate any assistance. I've tried searching for how to mimic SUMIFS functionality but I haven't understood enough to be able to apply it to my case.&nbsp;Thank you&nbsp;Excel File Link: &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; https://1drv.ms/x/s!AtJBzwB0OS_Qgzir0Ktk8KgkbQmy?e=3RBgsT

parry2k · Accepted Answer

Ptown&nbsp;see the attached solution, main point is to unpivot the data in power query and then it is easy to visualize
&nbsp;
Would appreciate Kudos 🙂 if my solution helped.
&nbsp;

Forum Discussion

Mimic Excel SUMIFS functionality to create a pipeline table (phases, sectors, actuals & targets)

2 Replies

Related content

Cumulative phasing

Deployment Pipelines

Scheduled pipelines failing

Phasing out the report

Periodic Cumulative phasing

Opportunity ID	Target or actual?	Value	Sector	Phase 1	Phase 2	Phase 3
Opp-001	Actual	£101.00	SEC-A	YES	YES	YES
Opp-002	Actual	£102.00	SEC-B		YES
Opp-003	Actual	£103.00	SEC-B

Phase	Target or actual?	SEC-A	SEC-B	SEC-C	SEC-D
Phase 1	Target	1000	1100	1200	1300
Phase 1	Actual	547	104	230	118
Phase 2	Target	2000	2100	2200	2300
Phase 2	Actual	316	326	343	110
Phase 3	Target	3000	3100	3200	3300
Phase 3	Actual	101	0	224	107