I am hoping some of you Power BI Desktop wizards out there can help me with this issue.
- The report I am creating is for visualisation of project portfolio funding.
- The portfolio hierarchy is the usual Program, SubProgram, Project tree.
- Funds are granted at the SubProgram level (whether or not it has child projects).
- As child projects come on line they are attributed a portion of funds from the parent SubProgram.
- The data is sourced from SharePoint lists (the following are Excel mock-ups):
a. SubProgram List
b. Project List
c. Cost List
Where the ProjID cell is empty this row is the Cost row for the SubProgram in the Financial Year (FY)
- Portfolio hierarchy funds are analysed in the Power BI report either by SubProgram funds or by Project funds by switching a SubProgram/Project row attribute filter.
- The funding model is different from what would be the correct model where the total for SubProgram funding is the natural SUM of the child projects.
- The sum of all child project funds in a given SubProgram often does not equal the funds recorded in the SubProgram as not all child projects have started.
- Report users get confused when switching the funds measure between SubProgram funds and Project funds as the totals shown by the Project funds source is usually less as not all child projects have been stood up.
- The correct solution would be to only record funding at the Project level. Unfortunately, business politics and funding mechanisms do not always follow logic….
- To me the best place to fix data issues is at the source (the SharePoint lists). So, one could create a placeholder Project (“Unallocated Funds Project”) for each SubProgram. This placeholder would hold the difference between the funds recorded at the SubProgram level and the SUM of the funds attributed to each of the child projects. The effect of this approach would be that switching between SubProgram and Project funds measures would not cause the Power BI graphs to jump.
- However, the SharePoint level solution requires an advanced workflow that would need to fire every time a record in the Cost list is updated. It would have to be automated as manual trigging would require people to remember…. We have neither the SharePoint coding skills nor the advanced workflow software to implement this solution.
- Is a solution in Power BI possible?
- I am open to either a DAX or Power Query solution (or a hybrid of both).
- Trying to solve this problem with DAX does my head in . I have reviewed the solutions for issues like “TopN and Others” but these solutions are effectively replacing category values with the word “Other” and using the existing measure. They are not manufacturing a new project for each SubProgram and populating it with data.