How to Sum by field on different table

[MyNameHere2213]

I'm trying to create a measure that will Sum the forecasted and actual proceeds a client receives for all of the companies they own. Data model is structured in a star schema with Clients as the fact table and all other tables as dimensional.

Data Model:

DimSubCompanies

• ClientID
• SubCompanyName

FactClients

• ID

DimPaymentActual

• PaymentID
• ClientID
• Net to Client

DimPaymentForecasted

• PaymentID
• ClientID
• Net to Client

The table I'm trying to make should look like this

SubCompanyName	Forecasted Net to Client	Actual Net to Client
SubCompany1	100	0
SubCompany2	500
SubCompany3	250	250

Instead the current table I have looks like more like this where it's summing the entire client across all the companies they own.

SubCompanyName	Forecasted Net to Client	Actual Net to Client
SubCompany1	850	250
SubCompany2	850	250
SubCompany3	850	250

How can I get my table to look like the first one?

[audreygerred]

Hi! In a star schema the DIM tables are the 1 side on 1:M. Your fact table should be the many and ideally, avoid bi-directional joins.

Is Client ID the key for Sub-category? What granularity level is that dim table at? What level of granularity is actual and forecast at?

[MyNameHere2213]

It's been a while since I've done this, you are correct maybe I should look at restructuring my model.

ClientID is a FK for the Client table that contains various information about the client (name, location, email, etc.).  The actual and forecasted tables are granular to a specific transaction and I can see what they should have received and actually received for every transaction that occurred.