Data Modeling for a Fact Table with multiple Account Distribution

[PBILix]

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
A	2	A
A	3	A
B	1	A
B	2	A
B	2	B
B	2	C

There are also accounts and projects related to this table. But a Order Position (and therefore its invoices) can be assigned to multiple accounts or projects (from a separate Table) with a percentgae distribution.

E.G. Position 1 of Order A is Assigned to Account Sales and Account MArketing each 50%. But a Position could also assigned to n accounts or projects How can i model this? The Percentage distribution is the same for accounts and projects. The distribution is then needed to calculate invoice and order amounts which are in the OrderInvoice fact table

Any Idea how i could model this with power query?

[v-ssriganesh]

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.

[v-ssriganesh]

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.

[PBILix]

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?

[v-ssriganesh]

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.