Creating Rows That do not Exist in Data Source

Power BI
1

Hi All,
I am hoping some of you Power BI Desktop wizards out there can help me with this issue.

Background

  1. The report I am creating is for visualisation of project portfolio funding.
  2. The portfolio hierarchy is the usual Program, SubProgram, Project tree.
  3. Funds are granted at the SubProgram level (whether or not it has child projects).
  4. As child projects come on line they are attributed a portion of funds from the parent SubProgram.
  5. The data is sourced from SharePoint lists (the following are Excel mock-ups):
    a. SubProgram List
    image

b. Project List
image

c. Cost List
image
Where the ProjID cell is empty this row is the Cost row for the SubProgram in the Financial Year (FY)

  1. 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 Issue

  1. 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.
  2. 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.
  3. 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.

Solution Options

  1. The correct solution would be to only record funding at the Project level. Unfortunately, business politics and funding mechanisms do not always follow logic….
  2. 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.
  3. 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.
  4. Is a solution in Power BI possible?
  5. I am open to either a DAX or Power Query solution (or a hybrid of both).
  6. Trying to solve this problem with DAX does my head in :grimacing:. 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.

I have attached the Excel dummy data and a Power BI report to help.
Thanks
Mark
Program Analysis Demo.pbix (105.3 KB) Program Data.xlsx (13.6 KB)

2

@MarkStafford I think you will find if the model does support business logic then you will run into these kinds of problems.

The key is how your reporting hierarchy is designed and how you apply the DAX in the child-parent relationship for the Report Hierarchy and the DAX measure to roll up the totals required.

So from my understanding Level 1 Parent is the Program Name. Level 2 would be the Sub Program and Level 3 would be the Project Name.

–Programs AAA (Level 1)
–Sub Programs AAAxxx (Level 2)
–Project Names AAAxxx-xxx (Level 3)

Does the above look correct?

Naturally, child accounts should represent the sum of the parents hence why its the parent so if don’t meet this logic it will be hard to build the correct visuals with the child-parent relationships in Power BI.

Your right it’s always best to fix the data in the source. The problem you have is similar to how we report on the job costs that are coded to sub-jobs within a hierarchical structure.

It’s almost like you need a master value to represent the Funds allocated to Sub Program and then expend these funds to the various projects as they get allocated. The difference would be unallocated. Two sets of facts tables here, one recording the initial funds and one recording the allocation to the project.

Are your fact tables able to be organised in this manner with the two workflows around the funding transactions?

3

@GarryA Hi Garry, Thank you for providing some input. You have the Project hierarchy correct. I am not kean on implenting a multi-fact solution due to the increased DAX complexity.

While waiting for responses to my plea for help I have been developing a solution from a Power Query perspective. I have designed a Power Query ETL that:

  1. creates a new “Unallocated Funds” child project for each SubProgram that has funding
  2. Creates an associated “Unallocated Funds” row in the Cost table for each FY that the SubProg has a row.
  3. Sums all of the Forecast values from the related child projects for each FY and subtacts these values from the related SubProg Forecast value for the same FY. Places the result in the Forecast $ field of the related “Unallocated Funds” project row for that FY.

Once I prove the design I will upload an example

4

@MarkStafford, I would have thought this would make the model a lot simpler to work with and less complex dax.

When I model accounting system data I always split facts into groups so I have control totals which accountants can agree core balances to.

You may find coding in your fact table to support this segregation and allow you to have this break-up but it’s hard to give solid advice without more detail about the data source and system in use.

Anyway, good luck with your solution though!:smiley: