Log In
Register
Home
Active Member
June 21, 2022
Offline
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!
Moderators
January 31, 2022
Offline
Hi Jay,
The file was not attached. Please try again and don't forget to press the "Start Upload" button.
Active Member
June 21, 2022
Offline
Hello Riny,
Sorry about that. Just uploaded the file.
Moderators
January 31, 2022
Offline
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.
Active Member
June 21, 2022
Offline
I wasn't sure how the Cumulative and remaining balance would work if I was to append the tables.
Active Member
June 21, 2022
Offline
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.
Moderators
January 31, 2022
Offline
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.
Active Member
June 21, 2022
Offline
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.
1 Guest(s)
