Hello,
My goal is to show and analyze a Task and it's monthly spending (bar chart), see the remaining (area chart) and compare against my contingency(line).
I have 6 tasks and 6 tables for (Task 01, 02, 04, 04.1, 05, and 08 (contingency) ). I have also created a filter table with those 6 tasks as unique identifiers and a Calendar table. I plan to use the two latter tables for filters/slicers.
I'd like to use my tasks as slicers so I can compare each task against contingency. I already added the columns for Cumulative and Remaining balance. I realize these could be done as measures but my data isn't too big.
Issues: I think I've gone about this the long way and kind of stuck on how to approach this. I've tried several ways but the cumulative isn't adding up to what it should.
I've attached my sample file and all the tables are in the same file, so should not be an issue with connection.
Look forward to the replies!
Hi Jay,
The file was not attached. Please try again and don't forget to press the "Start Upload" button.
Hello Riny,
Sorry about that. Just uploaded the file.
Not sure I follow but my first question would be why you don't append all the tables (in Excel or with PowerQuery) and create the pivot table from there.
I wasn't sure how the Cumulative and remaining balance would work if I was to append the tables.
I appended the tables into 1 and kept the Task08 table out since it's columns are differently organized. But for some reason the Pivot chart won't show the bars and area chart together.
To be honest, I have a hard time following where all the numbers come from. Especially the Budget Overrun column in the Contingency table and the meaning of the Draw and Credit columns. Just seems you are over-complicating the matter or I'm just not seeing it.
Budget overrun in each Task is when my Remaining Balance starts going negative. I have a budget table under the "All Tables" tab which I forgot to add to model.
Budget Overrun in the Contingency table is the combination across all tasks for that month. For example, in 12/1/22, I have Task 01, 02, and 04 all overrunning their budgets, so Contingency table shows Budget overruns for all three combined.
I was trying to see my net risk each month. Draw is how much I have to draw from contingency to offset the net risk and credit is credit I am receiving to the project, which backfills my contingency. I apologize, I should have clarified these before.
I'd appreciate any suggestions on making this less complicated.
