Forum Discussion
Project Online - Business Drivers matrix table in Power BI
Hi! After classifying our ongoing projects we could get to this matrix table in Project Online: There are 6 Business Drivers where each project contributes on a rating scale (None, Low, Moderat...
Here is my take on this given the limited info I have about your model and other requirements. If nothing else this may give you some ideas on how to accomplish your end goal.
I would build a new table in Power Query as follows.
- Load All Tables:
- Project
- PortfolioAnalysisProject
- PortfolioAnalysis
- PrioritizationDriver
- BusinessDriver
- Merge Queries:
- Start with Project
- Merge as New table with PortfolioAnalysisProject on ProjectId
- Merge with PortfolioAnalysis on AnalysisId
- Merge with PrioritizationDriver on PrioritizationId
- Merge with BusinessDriver on BusinessDriverId
- Select and Keep Only:
- Project[ProjectName]
- BusinessDriver[DriverName] (A–F)
- PrioritizationDriver[DriverValue] (None–Extreme)
- Name new table BuinessDriverMatrix?? and establish relationship to Project table
- Pivot the Table:
- Select DriverName column → Pivot Column
- Use DriverValue as the Values Column
- In the pivot settings, choose Don’t Aggregate (or use “Max” if needed)