Hello everyone,
I would like to ask for some help from the community, cause I have tried almost everything, but unfortunately I can't achieve the desired result.
I have an Excel file, with multiple sheets, which I use as a data source for PowerBI. In one of the sheets I have as columns, among others, the following (Table A):
Project ID, Project Name, Category (X different categories), Project Manager (Y different people), Location (Z different locations) and then each month starting from Jan-19 until Dec-23 with entries of the load the of the Project Manager for each of the upcoming months. Some Project Managers are on the same location and working on the same category
The unpivot option allowed me to bring the dates (Jan-19) and the Load as row entries to each project, which allowed me to plot the Load (y-axis) - Date (x-axis) chart, which is fine and works.
My problem is that now I want to add a line (plot a combo chart column + line), where I display the max workload (capacity) per i) category ii) project manager iii) location.
I have created a table (call it Table B), with columns: Project Manager, Category, Location, Capacity (all equals to 1) and Date, which allows me to plot the desired line ONLY when I plot the Load - Date chart, since I have connected table A with B via an active relationship between the dates.
If I want to drill down into a specific date (x-axis) from this chart to check what happens in a location (x-axis) or in a category (x-axis), then the line doesn't automatically adjust to reflect the capacities per location or per category. In other words, it sums up the capacity per date (where I have established an active relationship), but it doesn't do the same regarding location or category if I decide to drill down.
I have tried manually creating some tables and linking them but I always get either the line adjusted per location/category but the columns (load) wrong (the load is the monthly load for each location/category) or the line doesn't adjust and the load splits between category or location correctly.
I would greatly appreciate any ideas
