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:
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:
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!
Could you share some sample data and clarify more details about logic and expect result?
Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.