Help with calculation

[sw123]

Hi,

Can someone help me with this? I have three tables: Report, Holes and Recipe.

Report:

Report Id:

1

2

3

4

5

6

Holes:

Hole Id         Recipe Id           Report Id

1                   2                           1

2                   3                           1

3                   3                           1                          

4                   2                           1

5                   1                           2

6                   4                           2

Recipe:

Recipe Id      Preset kg

1                   1

2                   4

3                   2

4                   8

I would now 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).

How can I calculate this?

[lbendlin]

Can there be more than one charging report for a hole?  Can there be more than one hole per charging report?

[sw123]

Every report has holes starting from 0, 1, 2, 3, 4. I want to calculate the preset kg:s / report, into a table.

There can be two different holes, with the same number on one report with different recipes (two different lines per report). We do have one column in the Hole-table, that telss which line we are talking about.

[lbendlin]

yeah, this is too complex of a data model. Beyond my capabilities, sorry.

[sw123]

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?

[lbendlin]

Please provide sanitized sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[sw123]

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).

[lbendlin]

What's the rationale behind considering ChargingReports a fact table?

[sw123]

I don´t know if any of the tables are actual Fact tables, since it creates new rows for all of these tables every time you charge a hole. 

[parry2k]

@sw123 although @lbendlin solution will work and it is a great solution but another approach could be to avoid adding a new column in the Holes table, you can achieve this by using the following measure, and then use this measure in the visualization:

Preset Kg = SUMX ( Holes, RELATED ( Recipe[Preset KG] ) )

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

[lbendlin]

You create a standard data model

Then you pull in the kg value from the Recipes dimension into the Holes fact as a calculated column

kg = related(Recipes[Preset kg])

Then you can let the table visual do the rest of the work