Forum Discussion
Help with calculation
Thank you, both. Unfortunately I cannot get either suggestion to work. I guess it is because my data is a bit more complicated, than my simplified example. The Recipes-table includes the same Recipe ID many times. Since the same Recipe can be changed, the Recipe-table gets a new row for every time a Recipe is used in a report. Like this:
Recipes-table:
Id Recipe ID Report ID Preset KG
1 1 1 4
2 1 2 4
3 2 2 3
4 2 3 3
To get this to work, I have made another table: SharedDimRecipeId, which takes the Recipe ID from the Holes-table and removes duplicates. Then I have many-to-one relationships from the Recipes-table and the Holes-table to this new SharedDimRecipeID.
Any ideas?
Please provide sanitized sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.
Ok, here is a picture of the actual data model:
And here is my problem explained again:
FactChargingReport:
Report Id:
1
2
3
4
5
6
FactHoleData:
Hole Id Recipe Id Report Id
1 2 1
2 3 1
3 3 1
4 2 1
5 1 2
6 4 2
FactRecipe-table:
Id Recipe ID Report ID Preset KG
1 1 1 1
2 1 2 4
3 2 2 2
4 2 3 8
SharedDimRecipeIdentifier-table
Recipe ID
1
2
3
4
5
I would need to calculate the summarized Preset kg:s / Report. (In this example the summarized preset kg:s for Report 1 would be 12 (Recipe 2: 2 holes x preset kg 4 = 8 + Recipe 3: 2 holes x preset kg 2 = 4. 8+4=12) and for report 2: 9 (Recipe 1: 1 hole x preset kg 1 = 1 + Recipe 4: 1 hole x preset kg 8 = 9. 1+8=9).