babyjwhale
New Member
- Joined
- Nov 6, 2024
- Messages
- 6
- Office Version
- 365
- Platform
- Windows
Overview:
I'm in the construction business, and I'm trying to build a database to keep track of project costs by project. I classify costs using a 7-tier hierarchy that is roughly 250 lines long, though many will not be used on each project. I want to build a dashboard that shows the historical cost data for various projects that I select/filter. I envision this being a pivot table or combination of pivot tables. Each project will have fact inputs (project name, project type, building size, land area, etc.) and cost data, with inputs that will be correctly "filed" by the 7-tier hierarchy structure. The cost data for each project will be reported as a whole dollar and as a dollar per square foot (pulled from project facts).
On the dashboard, I want to be able to filter all projects with multiple criteria (for example, show all Industrial projects that are greater than 50,000 square feet that were priced in the past 3 years), or I want to be able to simply multi-select by project ID for specific projects that I want to show at one time. I would also like to be able to show a variance between selected projects and a Baseline project. Ideally, the Baseline can be a drop down select of the project name.
Each project data set will be manually and statically input into a form with facts at the top and price inputs below. The input format will basically be a copy of the 7-tier hierarchy with manual inputs in a single column for whole dollar price inputs beside it. We may add 100 project pricing datasets per year.
I envision the "master sheet" to have three tabs: 1) the dashboard, 2) the master classification hierarchy, and 3) the data.
Specific Questions:
1) I don't know the best way to translate single cell facts to all pricing data for the project. I've considered Power Query or just adding table columns that reference the facts, since there are only a small number of them.
2) I'm thinking Power Pivot will be used to relate the 7-tier hierarchy and the dataset for rolling up the data. Am I correct in thinking this?
3) I'd don't know the best way to bring in a new project dataset to the master dataset. I lean towards using VBA or a macro to copy the project data into the master data and then clear out the project inputs (to allow for the next input). I don't think that I want to use Power Query referencing a folder with individual workbooks of project data because there will eventually be so many workbooks; plus, the data in the workbooks doesn't change, so static one-time is sufficient. Am I correct in thinking VBA? Any pointers on where to look to learn how to do this? I'm new in VBA...
4) I don't know how to create the Baseline selection for Variance. I want to be able to compare multiple projects to the Baseline selected project, not just one project, so I'm not sure how that calculation will work. I'm thinking that's a Power Pivot expression, but not sure. Any advice here is appreciated.
Simple Vision of Pivot Table:
I'm in the construction business, and I'm trying to build a database to keep track of project costs by project. I classify costs using a 7-tier hierarchy that is roughly 250 lines long, though many will not be used on each project. I want to build a dashboard that shows the historical cost data for various projects that I select/filter. I envision this being a pivot table or combination of pivot tables. Each project will have fact inputs (project name, project type, building size, land area, etc.) and cost data, with inputs that will be correctly "filed" by the 7-tier hierarchy structure. The cost data for each project will be reported as a whole dollar and as a dollar per square foot (pulled from project facts).
On the dashboard, I want to be able to filter all projects with multiple criteria (for example, show all Industrial projects that are greater than 50,000 square feet that were priced in the past 3 years), or I want to be able to simply multi-select by project ID for specific projects that I want to show at one time. I would also like to be able to show a variance between selected projects and a Baseline project. Ideally, the Baseline can be a drop down select of the project name.
Each project data set will be manually and statically input into a form with facts at the top and price inputs below. The input format will basically be a copy of the 7-tier hierarchy with manual inputs in a single column for whole dollar price inputs beside it. We may add 100 project pricing datasets per year.
I envision the "master sheet" to have three tabs: 1) the dashboard, 2) the master classification hierarchy, and 3) the data.
Specific Questions:
1) I don't know the best way to translate single cell facts to all pricing data for the project. I've considered Power Query or just adding table columns that reference the facts, since there are only a small number of them.
2) I'm thinking Power Pivot will be used to relate the 7-tier hierarchy and the dataset for rolling up the data. Am I correct in thinking this?
3) I'd don't know the best way to bring in a new project dataset to the master dataset. I lean towards using VBA or a macro to copy the project data into the master data and then clear out the project inputs (to allow for the next input). I don't think that I want to use Power Query referencing a folder with individual workbooks of project data because there will eventually be so many workbooks; plus, the data in the workbooks doesn't change, so static one-time is sufficient. Am I correct in thinking VBA? Any pointers on where to look to learn how to do this? I'm new in VBA...
4) I don't know how to create the Baseline selection for Variance. I want to be able to compare multiple projects to the Baseline selected project, not just one project, so I'm not sure how that calculation will work. I'm thinking that's a Power Pivot expression, but not sure. Any advice here is appreciated.
Simple Vision of Pivot Table:
| Test File.xlsx | ||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| E | F | G | H | I | J | K | L | M | N | O | P | |||
| 6 | General Look of Desired Pivot Table | |||||||||||||
| 7 | Notes | |||||||||||||
| 8 | Project Name | Project A | Project A | Project A | Project A | Project B | ||||||||
| 9 | Project Type | Industrial | Industrial | Industrial | Industrial | Retail | ||||||||
| 10 | Pricing Type | Actuals | Bid | Bid | Budget | Actuals | ||||||||
| 11 | Pricing ID Note | Final | GC 1 | GC 2 | Prelim | Final | ||||||||
| 12 | hide; use for selects | Pricing ID | Project A - Actuals - Final | Project A - Bid - GC 1 | Project A - Bid - GC 2 | Project A - Budget - Prelim | Project B - Actuals - Final | |||||||
| 13 | use for filter | Pricing Date | 11/7/2024 | 9/6/2024 | 9/6/2024 | 5/1/2024 | 8/1/2023 | |||||||
| 14 | use for filter | Building Size | 50,000 sf | 50,000 sf | 50,000 sf | 50,000 sf | 75,000 sf | |||||||
| 15 | use for filter | Land Area | 10.1 ac | 10.1 ac | 10.1 ac | 10.1 ac | 15.0 ac | |||||||
| 16 | Price | Price / SF | Price | Price / SF | Price | Price / SF | Price | Price / SF | Price | Price / SF | ||||
| 17 | Price Input 1 | $ 150,000 | 3.00 /sf | $ 100,000 | 2.00 /sf | $ 110,000 | 2.20 /sf | $ 85,000 | 1.70 /sf | $ 225,000 | 3.00 /sf | |||
| 18 | Price Input 2 | $ 45,000 | 0.90 /sf | $ 20,000 | 0.40 /sf | $ 35,000 | 0.70 /sf | $ 50,000 | 1.00 /sf | $ 60,000 | 0.80 /sf | |||
| 19 | Price Input 3 | $ 2,900,000 | 58.00 /sf | $ 3,000,000 | 60.00 /sf | $ 2,750,000 | 55.00 /sf | $ 3,500,000 | 70.00 /sf | $ 4,000,000 | 53.33 /sf | |||
Sheet1 | ||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| G12,O12,M12,K12,I12 | G12 | =TEXTJOIN(" - ",TRUE,G8,G10:G11) |
| H17:H19,P17:P19,N17:N19,L17:L19,J17:J19 | H17 | =G17/G$14 |
