Hi guys, I'm having issues with setting up my data model in order to meet my organisation's reporting requirements and hope you can advise.
We manage 200+ projects across 2x locations. Each project is classifed as Type A or Type B in the organisation's annual budget. Some projects "carry over" across financial years, so are re-approved in the following year budget under a different classification, Type C. For example:
- Project #1 is located at Territory X.
- Project #1 was approved in the 2018 budget as Type A for $10m.
- Only $8m was spent on Project #1 in 2018.
- The balance of $2m is approved in the 2019 budget as Type C (alongside other, newer projects that are Type A and Type B in 2019).
In my Power BI report, I need to show the Actual Spend for each Project by Location for any given month in any given Financial Year via slicer, taking into account the Project Classification that changes year-on-year. I also need to be able to aggregate the Actual Spend for all of the Projects in question and show the total Actual Spend vs. total approved budget by Location and Financial Year. (Items in italics = data in columns that exist in various tables.)
Using Project #1 above as an example, the expected outcomes are:
- When the report slicers are set to Financial Year 2018, and Project Classification Type A, and Location Territory X, I expect to see that $10m for this project is included in the total, aggregate approved budget for all Type A projects at Territory X, and that $8m is included in the aggregated total Actual Spend for Type A projects based at Territory X in 2018.
- When the report slicer is set to Financial Year 2019, I now expect that the remaining $2m is included in the total, aggregate approved budget for all Type C projects at Territory X. (And the same applies for total actual spend for Type C in 2019.)
- When I drillthrough to the details, I expect to see a page that shows the financial details for Project #1 over the entirety of its life. An approved budget of $10m, of which $8m was spent in 2018, and a graph/slicers to show how much was spent in which month, and $2m balance to spend in 2019. Then a card that shows its location at Territory X.
I am stuck getting the relationships to work properly - whilst Project #1 has a unique identifying value (#1), in my Budget table Project#1 appears twice, as Type A for $10m in 2018, then again as Type C for $2m in 2019. The SAP report I use as the source table for actual dollars spent only references spend by project number, not by Location. To make matters worse, this SAP report includes other Locations that are not relevant to my reporting needs!
At the moment, I can only get three of Actual Spend , Project Classification, Financial Year , and Location working together, when I need all four of them working seamlessly. Can you please help me on the best and/or simplest way of achieving my desired relationship? I have the luxury of building this data model and relationships from the ground up, so can change the tables and columns around if required.
Thanks in advance!
