Forum Discussion
Figuring out a data model
Hi all,
Struggling with this one, although I might be overthinking it.
My data model has three tables listed below. I need to create a matrix with the following columns:
a. Stage name
a1. Sub-stage
b. Expected expense
c. Spent.
How can I ensure that all Stage Names are used, and when a row from the Fact Table such as the ones that have Stage 500 don't have a matching value in the Expected Expense table they will still show up in the matrix and vice versa? I'm thinking it would need to be a union of the Stage in tables 1 and 3 and duplicates removed.
Any ideas would be welcome.
1. Expected Expense:
| Project ID | Stage | Sub Stage | Expected Expense | Stage Name |
| 123456 | 100 | 160 | 10000 | Phase 1 |
| 123456 | 100 | 180 | 20000 | Phase 1 |
| 123456 | 200 | 210 | 30000 | Phase 2 |
| 123456 | 200 | 230 | 40000 | Phase 2 |
| 123456 | 300 | 310 | 50000 | Phase 3 |
| 123456 | 300 | 320 | 60000 | Phase 3 |
| 123456 | 400 | 410 | 70000 | Phase 4 |
| 123456 | 400 | 420 | 80000 | Phase 4 |
2. Stage and Sub-Stage master list:
| Stage | Sub Stage |
| 100 | 160 |
| 100 | 180 |
| 200 | 210 |
| 200 | 230 |
| 300 | 310 |
| 300 | 320 |
| 400 | 410 |
| 400 | 420 |
| 500 | 510 |
| SUV100 | SUV110 |
| SUV200 | SUV220 |
3. Fact Table:
| Spent | Stage | Sub Stage | Project ID |
| 3516 | 100 | 160 | 123456 |
| 574 | 100 | 160 | 123456 |
| 4108 | 100 | 160 | 123456 |
| 539 | 100 | 180 | 123456 |
| 122 | 200 | 230 | 123456 |
| 1420 | 200 | 230 | 123456 |
| 242 | 200 | 230 | 123456 |
| 2652 | 300 | 310 | 123456 |
| 2953 | 300 | 310 | 123456 |
| 4015 | 400 | 410 | 123456 |
| 4742 | 400 | 410 | 123456 |
| 2535 | 500 | 510 | 123456 |
| 4854 | 500 | 510 | 123456 |
| 2656 | 500 | 510 | 123456 |
Hi PoetryinMotion1 ,
I created a sample pbix file(see the attachment), please check if that is what you want.
Best Regardsd
1 Reply
Hi PoetryinMotion1 ,
I created a sample pbix file(see the attachment), please check if that is what you want.
Best Regardsd