Forum Discussion
Data Modeling for a Fact Table with multiple Account Distribution
Hello PBILix,
Thanks for the clarification. Based on your scenario, here’s the recommended approach to handle percentage-based allocations while preserving a clean dimensional model:- Create a separate Bridge_Distribution table that maps Order + Position to multiple Accounts or Projects with a percentage column. This avoids duplicating your fact or dimension tables.
- Your Dim_Account, Dim_Project, etc., should only contain unique IDs and names. No percentage or duplication logic should exist in these tables.
- Link Fact_Invoice to the Bridge_Distribution on Order + Position and connect the bridge to dimension tables on AccountID / ProjectID. This preserves clarity and scalability.
- In Power Query or DAX, merge the invoice fact table with the bridge table and calculate AllocatedAmount = Amount * Percentage for reporting.
This approach eliminates many-to-many complications between facts and dimensions, while still enabling accurate allocation and filtering.
Thanks for your fast and helpful reply.
In my scenario
| Order | Position | Invoice |
| A | 1 | A |
| A | 1 | B |
| A | 2 | A |
| A | 3 | A |
| B | 1 | A |
| B | 2 | A |
| B | 2 | B |
| B | 2 | C |
I have multiple invoices for each position.
In the report I need dimension tables with Account Name & ID, Project Name & ID etc. which are connected to the fact table.
Currently I am using a classic star schema, but with a simple one to many relationship without the percentage distributed values.
Now, I would have duplicates in my dimension and fact tables and a many to many relationship.
Would a bridge table be helpful here? When I add the percentage distribution to the dimension tables (account, project, cost center etc.) and then merge a new table with my fact table based on Order & ID?
Hello PBILix,
Thanks for the clarification. Based on your scenario, here’s the recommended approach to handle percentage-based allocations while preserving a clean dimensional model:
- Create a separate Bridge_Distribution table that maps Order + Position to multiple Accounts or Projects with a percentage column. This avoids duplicating your fact or dimension tables.
- Your Dim_Account, Dim_Project, etc., should only contain unique IDs and names. No percentage or duplication logic should exist in these tables.
- Link Fact_Invoice to the Bridge_Distribution on Order + Position and connect the bridge to dimension tables on AccountID / ProjectID. This preserves clarity and scalability.
- In Power Query or DAX, merge the invoice fact table with the bridge table and calculate AllocatedAmount = Amount * Percentage for reporting.
This approach eliminates many-to-many complications between facts and dimensions, while still enabling accurate allocation and filtering.