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
- PaymentID
DimPaymentActual
- PaymentID
- Net to Client
DimPaymentForecasted
- PaymentID
- 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 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?