Time for me to learn BI? Building a weekly variance report for multiple WBS levels.
I have 3 data sets that I'm trying to build a sliceable visual for, the data coming in at mixed WBS ( work breakdown structure) level is what is throwing me for a loop. I have 52 periods, 15 Business Units, and will have upwards of 300 projects (all of which will come in to the data set at different times) by the time the fiscal year ends, the below is a simplified representation of my data.
I want to be able to run variances to plan at the grand total level and at the Business Unit level, I then want to be able to drill down to the specific projects that make up the actuals data though obviously there is no plan to compare to at that level. I've tried running a power query and building a pivot of the resulting data and maybe it's just how I'm arranging it but I haven't found something that works, I need to create some calculated fields to calculate the positive and negative variance from actuals to plan for my visual but because of the way the data is arranged I can't select "Plan" or "Actual" as fields since they all under the source field in my power query that I tried using to get around the misaligned data.
The ultimate goal is to have 3 clustered columns (plan, revised plan, actuals) for each period with an arrow indicating the variance amount and visual indicating positive or negative (I have this part figured out). I then want to be able select a filter to show just Business 2 and have the visual update to reflect just that information.
The plan is strictly at WBS level 1 (BU)
| Plan Data Set | Period 1 | Period 2 | Period 3 |
|---|---|---|---|
| Business Unit 1 | 100 | 300 | 300 |
| Business Unit 2 | 100 | 250 | 200 |
| Business Unit 3 | 150 | 100 | 400 |
The revised plan is strictly at WBS level 1 (BU)
| Plan Data Set | Period 1 | Period 2 | Period 3 |
|---|---|---|---|
| Business Unit 1 | 100 | 300 | 300 |
| Business Unit 2 | 100 | 250 | 200 |
| Business Unit 3 | 0 | 0 | 200 |
My actuals will only come in at the project level (WBS Level 2) , each project has an associated BU tie
| Actual Data Set (thru P1) | Period 1 | Period 2 | Period 3 |
|---|---|---|---|
| BU1 Project 1 | 100 | 0 | 0 |
| BU2 Project 1 | 75 | 0 | 0 |
| BU2 Project 2 | 25 | 0 | 0 |
| BU3 Project 1 | 25 | 0 | 0 |
| BU3 Project 2 | 50 | 0 | 0 |
| BU3 Project 3 | 75 | 0 | 0 |
My Power Query Result looks something likes this (just typed out Period 1)
| Source | Business Unit | Project | Period | Hours |
|---|---|---|---|---|
| Plan | BU1 | Period 1 | 100 | |
| Plan | BU2 | Period 1 | 100 | |
| Plan | BU3 | Period 1 | 150 | |
| Revised | BU1 | Period 1 | 100 | |
| Revised | BU2 | Period 1 | 100 | |
| Revised | BU3 | Period 1 | 0 | |
| Actual | BU1 | BU1 Project 1 | Period 1 | 100 |
| Actual | BU2 | BU2 Project 1 | Period 1 | 75 |
| Actual | BU2 | BU2 Project 2 | Period 1 | 25 |
| Actual | BU3 | BU3 Project 1 | Period 1 | 25 |
| Actual | BU3 | BU3 Project 2 | Period 1 | 50 |
| Actual | BU3 | BU3 Project 3 | Period 1 | 75 |
[link] [comments]
Want to read more?
Check out the full article on the original site