Hi I have been trying to establish the right model that will hopefully give me the following output:
- Establish the correct relationship between actuals and the forecast tables in order to run some standard comparison metrics .
- Be able to filter by person, project
- Planned Headcount as a percentage and being able to compare this against the actual hours booked by an engineer to a project.
- Once I guess the foundation / model is structured my thoughts are to add additional tables such as project status, risks and defects into the mix.
- The cost analysis I’m fairly content can be done in DAX from some of the great instructional videos that I’ve seen over the last month on here.
Results: so far I have constructed and deconstructed and built again but only seem to get one side of data, either actual or the forecast. I have tried unique IDs in each fact table and then create a bridge via a new query and creating a unique column, which I did manage to create a one to many between the two but was unsuccessful once I tried it in a table in visualisation.
Even asking and posting this doesn’t sit well as I do love a challenge. I can only assure you that I have placed considerable time and effort (weeks lol) to get past this issue but its time to get some help ’ please’.
Due to data constraints I am unable to submit the actual data, however I have constructed some tables representing some columns that appear in the originals that I hoping along with the above text to help people understand what I need.
Thank you and pleas be kind, you can probably tell I very new to this but loving the journey