Forum Discussion
How to Sum by field on different table
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?
2 Replies
- audreygerred
Super User
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?
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.
