Forum Discussion
Trying to learn how to build a model - help requested
This is finance related. I have two tables for a business. One table is the budgetary table and the other is the transaction table.
The budget is decided by several elements. As an example, the 'transportation' budget might be $500,000 but divided into four regions or six categories.
The MISC Expenses budget might be $100,000 but split into several categories also.
Here are example tables (incomplete):
Budget:
| Year | Account | State | District | FundType | Element | Manager | Budget | Q1 | Q2 | Q3 | Q4 |
| 2023 | Main | TX | North | D01 | F | Dave | 10000 | 2000 | 2000 | 1000 | 1000 |
| 2023 | Main | TX | South | D02 | G | Brian | 15000 | 5000 | 3000 | 0 | 0 |
| 2023 | Main | FL | North | D01 | G | Brian | 10000 | 0 | 5000 | 5000 | 0 |
| 2023 | Trans | TX | North | D01 | F | Brian | 5000 | 2000 | 1000 | 500 | 500 |
| 2023 | Trans | TX | North | D02 | F | Dave | 8000 | 2000 | 5000 | 1000 | 0 |
| 2023 | Trans | TX | South | D01 | F | Dave | 5000 | 1500 | 0 | 0 | 1500 |
| 2023 | Trans | FL | North | D01 | G | Dave | 4000 | 1000 | 1000 | 1000 | 1000 |
| 2023 | Misc | TX | North | D02 | G | Dave | 2000 | 500 | 500 | 1000 | 0 |
| 2023 | Misc | TX | South | D02 | G | Brian | 3000 | 1000 | 500 | 500 | 500 |
| 2023 | Misc | FL | South | D02 | F | Brian | 6000 | 0 | 0 | 3000 | 2000 |
Transactions (incomplete):
| Year | Account | State | District | FundType | Element | Manager | Quarter | Transaction | Description |
| 2023 | Trans | TX | North | D01 | F | Brian | Q1 | 100 | ........ |
| 2023 | Trans | TX | North | D01 | F | Brian | Q1 | 150 | ........ |
| 2023 | Trans | TX | North | D01 | F | Brian | Q2 | 100 | ........ |
| 2023 | Trans | FL | North | D01 | G | Dave | Q2 | 50 | ........ |
| 2023 | Main | TX | North | D01 | F | Dave | Q1 | 50 | ........ |
| 2023 | Trans | TX | North | D01 | F | Brian | Q1 | 200 | ........ |
| 2023 | Trans | FL | North | D01 | G | Dave | Q1 | 250 | ........ |
| 2023 | Trans | TX | North | D01 | F | Brian | Q1 | 50 | ........ |
| 2023 | Misc | FL | South | D02 | F | Brian | Q2 | 100 | ........ |
There are several categorical elements [Account, State, District, FundType, Element, Manager] and then Quarter and Year for time-series.
What I want to do is create a dimension table for the unique values. The problem I am having is if there are other transaction tables, then I would need to use inactive relationships with 'USERELATIONSHIP'.
I would like to create tables that view per categories, sum the budget table and subtract the transactions from the total budget.
Example:
| District | Budet | Transaction | Remaining Budget |
| North | $100000 | $43520 | $56480 |
| South | $85000 | $22500 | $62500 |
In order to do this, I would create a measure for the DistrictBudget that sums the budget for the district and adds that Measure to this table. But then i would need to repeat that for each categorical unit and I imagine there is a more efficient way.
At the end, what I have to start is 2+ fact tables with many categorical columns to run analysis by. I have no standard dimension tables.
The most important element is calculating the budget in a same table as the transactions to calculate the remaining budget and the time-series rate for which the budget is being spent.
3 Replies
- DataInsights
Super User
Creating a star schema with fact and dimension tables is the preferred design. A dimension table should exist for each dimension (e.g., DimDistrict, DimFundType). You can create dimension tables in Power Query or DAX. Select distinct values for a dimension from both fact tables, and that table will become your dimension table. For example, select distinct District from each fact table, append those two tables, and select distinct District (this ensures that you have all possible District values across both fact tables). In the end, you will have multiple dimension tables, and each dimension table will have a one-to-many relationship with each fact table. You shouldn't need to create inactive relationships.
The article below explains how to create a date table. I would add a date column to each fact table so you can create a relationship with DimDate. You can use the first day of the month (e.g., 2023 Q1 becomes 1/1/2023). The quarter columns in the Budget table need to be unpivoted so you have a single Quarter column.
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
Once the data model has been completed, create measures, and use dimension table fields in visuals.
PBInonproHelper III
Fiscal Year?
- DataInsights
