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

[Ptown]

Hi,

I'm struggling to recreate some functionality that I have in Excel. Any help would be greatly appreciated.

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.

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.

------

The current format I have got to, is a list of opportunities which have the following info:

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

...and so on.

---

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:

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

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.

I would like to remove this manual step using Excel  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.

Thank you

Excel File Link:             https://1drv.ms/x/s!AtJBzwB0OS_Qgzir0Ktk8KgkbQmy?e=3RBgsT

[parry2k · Accepted solution]

@Ptown see the attached solution, main point is to unpivot the data in power query and then it is easy to visualize

Would appreciate Kudos 🙂 if my solution helped.

[Ptown]

Hi parry2k,

That's great, thank you very much. 

P

-------

Note: I was only able to open the file on my personal laptop. On my work machine (which has Version: 2.75.5649.861 64-bit (November 2019)) I get the following error. But not a big deal. Thanks again!

Feedback Type:
Frown (Error)

Error Message:
Object reference not set to an instance of an object.

Stack Trace:
   at Microsoft.Mashup.Host.Document.PackageValidationResult.Merge(PackageValidationResult original, PackageValidationResult content)
   at Microsoft.PowerBI.Client.Windows.Services.ReportFactory.CreateReportFromPowerBIPackage(IPowerBIPackage powerBIPackage, String pbixFilePath, Byte[] securityBindings, Boolean requiresConversionToReportLayout, PowerBIPackageOpenOptions options, IPowerBITelemetryService powerBITelemetryService, IAnalysisServicesService analysisServices, Version pbixFileVersion, PackageValidationResult& mashupValidationResult)
   at Microsoft.PowerBI.Client.Windows.Services.PowerBIPackagingService.Open(FileStream fileStream, IPowerBIWindowService windowService, Nullable`1 entryPoint, PowerBIPackageOpenOptions options, ReportPreparerResult& prepareResult)
   at Microsoft.PowerBI.Client.Windows.Services.FileManager.<LoadFromPbix>d__20.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.PowerBI.Client.Windows.Services.UIBlockingService.<>c__DisplayClass15_0`1.<<BlockUIAndRun>b__0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at Microsoft.PowerBI.Client.Windows.Services.UIBlockingService.WaitOnUIThreadForTaskCompletion(Task task)
   at Microsoft.PowerBI.Client.Windows.Services.UIBlockingService.BlockUIAndRun[T](Func`1 asyncMethod)
   at Microsoft.PowerBI.Client.Windows.Services.FileManager.OpenFile(IPowerBIWindowService windowService, IPbixFile fileToOpen, Nullable`1 entryPoint)
   at Microsoft.PowerBI.Client.Program.TryOpenOrCreateReport(IUIHost uiHost, ISplashScreen splashScreen, IFileManager fileManager, IFileHistoryManager fileHistoryManager, String filePath, FileType fileType)
   at Microsoft.PowerBI.Client.Program.<>c__DisplayClass2_0.<Main>b__1()
   at Microsoft.PowerBI.Client.Windows.IExceptionHandlerExtensions.<>c__DisplayClass3_0.<HandleExceptionsWithNestedTasks>b__0()
   at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)

Stack Trace Message:
Object reference not set to an instance of an object.

Invocation Stack Trace:
   at Microsoft.Mashup.Host.Document.ExceptionExtensions.GetCurrentInvocationStackTrace()
   at Microsoft.Mashup.Client.UI.Shared.StackTraceInfo..ctor(String exceptionStackTrace, String invocationStackTrace, String exceptionMessage)
   at Microsoft.PowerBI.Client.Windows.Telemetry.PowerBIUserFeedbackServices.GetStackTraceInfo(Exception e)
   at Microsoft.PowerBI.Client.Windows.Telemetry.PowerBIUserFeedbackServices.ReportException(IWindowHandle activeWindow, IUIHost uiHost, FeedbackPackageInfo feedbackPackageInfo, Exception e, Boolean useGDICapture)
   at Microsoft.Mashup.Client.UI.Shared.UnexpectedExceptionHandler.<>c__DisplayClass14_0.<HandleException>b__0()
   at Microsoft.Mashup.Client.UI.Shared.UnexpectedExceptionHandler.HandleException(Exception e)
   at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
   at Microsoft.PowerBI.Client.Program.Main(String[] args)