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.
Hello PBILix,
Thank you for reaching out to the Microsoft Fabric Forum Community.
I’ve reproduced your use case in Power BI using sample data that matches your structure and logic.
The final visuals and matrix outputs matched your requirement:
- Invoices were split across multiple accounts/projects correctly
- Allocated amounts summed up to match total invoice values
- Slicers by EntityType (Account / Project) and breakdown by EntityName work as expected
I've attached a .pbix file to this post so you can explore the setup, Power Query steps, relationships, and DAX measures directly.
If this information is helpful, please “Accept as solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.
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.