Forum Discussion
Solved
Data Modeling for a Fact Table with multiple Account Distribution
Hello Community, I am using Power BI and Power Query with SAP Data. In my Fact Table I have a OrderInvoice Table on a invoice level granularity. Order Position Invoice A 1 A A 1 B...
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,
Hope everything’s going great on your end! Just checking in has the issue been resolved, or are you still running into problems? Sharing an update can really help others facing the same thing.
Thank you.