This example has the following conditions:
- Show the Total Sales during a period of time
- Show the Average Sales during a period of time
A straight table showing the results:Sum of Sales
is showing the correct value 2+16+5+4+4+2+7+11=51Average Sales
is showing 5,1 i.e. 51/10=5,1 and that seems to be correct according to the table above, but the value isn't correct based on the loaded data.
From the source table Transaction
there are only 8 Sales
occurrences. Expected result for Average Sales
The issue appears because of a synthetic key in the data model. The synthetic key is show as table named with $Syn
Two extra rows appears:
This happens because of the synthetic key and is a result of values found in table SalesPerson
The data model must be adjusted so that the two table only have one common field, which will resolve the synthetic key.
In this case only the field SalesPerson
in table SalesPerson
will be loaded.
The synthetic key is resolved and a new key created:
Both Sum of Sales
and Average Sales
are now displaying the expected and correct values:
Synthetic keys aren't always the root cause when something goes wrong but it can be hard to foresee what the impact might be. Therefore, always make sure that synthetic keys are removed from the data model.