Handling multiple FACT tables in data model?

[jd8766]

Hello, hopefully someone can advise me on this (I am not new to PBI, just the data modelling side..)

I have an existing model that looks like the below - a Sales Fact table connected to multiple dimensions. It's relatively simple..

I have been given 2 new tables to add which can be joined via the ContractKey. One is a payments table that shows all payments we have had per contractkey, and one is a paymentsschedule table to show all payments we expect to have (so we can see if we are being paid on time or not)

What would be the best way to incorporate these into my model? So I can query things such as sum(paymentamounts) / sum(paymentexpectedamount) by supplier or product. Another one I can see being asked for is SUM(PaymentAmount) / SUM[Sales'Value'] as this would show the % paid off each contract..

Just looking for some opinions on the best way of approaching this really.. would a new FACT table work which could then be joined via the contract dimension?

[DataInsights]

@jd8766,

One approach would be to join these two new tables to the contract dimension. Then use CROSSFILTER to calculate the measures you mentioned:

Payment Amount = SUM ( FactPayment[PaymentAmount] )

Payment Expected Amount = SUM ( FactPaymentSchedule[PaymentExpectedAmount] )

Payment Amount ÷ Payment Expected Amount = 
CALCULATE (
    DIVIDE ( [Payment Amount], [Payment Expected Amount] ),
    CROSSFILTER ( DimContract[ContractKey], FactSales[ContractKey], BOTH )
)