I'm new to Quicksight (having used PowerBI for years) and am struggling to understand why relationships in datasets are always treated as JOINs and not related tables. It makes calculations very difficult. For instance, if I have two tables in a 1:M parent:child relationship and I try to SUM a field on the parent table I get a larger result than expected because of the 1:M join on the child.
In the example situation below, if I were to look for the SUM of the Annual Revenue, I would expect it to be $3,000, but instead I get $7,000 because of the join.
Am I missing something here?
Example
Table 1 - Accounts
| AccountID | Annual Revenue |
|---|---|
| ABC | $1,000 |
| XYZ | $2,000 |
Table 2 - Opportunities
| AccountID | Opportunity Amount |
|---|---|
| ABC | $100 |
| ABC | $200 |
| ABC | $300 |
| XYZ | $100 |
| XYZ | $200 |
