Hey guys,
I'm searching for help which might be easy for you, but costs days for me to find out a solution...
Original data I have like this in 2 tables.
How Can I calculate / show the total copper weight and silver weight in each month?
I first try to first calculate the copper and silver weight each line item by creating a new measure:
sum(delivery quantity)*[copper weight / part]
then I have difficulty to have a sum of each month based on this formel.
I tried then the function:
sum(Aggr(Sum([Delivery Quantity])*[copper weight / part], [Supplier],[Part]))
But it gives me wrong figure in each month if I show them in a pivot table, however the total sum of all months is correct. Only if I select the exact month, I get the correct value. Why???
Really hope I can get a quick answer.
Table 1: Part master data
| Part | Supplier | Copper weight / part | Silver weight / part |
| x | A | 0,1 | 0,002 |
| y | B | | 0,006 |
| z | C | 0,4 | 0,004 |
| w | C | 0 | 0 |
Table 2: delivery quantity each month
| Part | Supplier | Month | Delivery quantity |
| x | A | Oct | 1 |
| x | A | Nov | 2 |
| x | A | Dec | 1 |
| y | B | Oct | 1 |
| y | B | Nov | 1 |
| y | B | Dec | 4 |
| z | C | Oct | 2 |
| z | C | Nov | 1 |
| z | C | Dec | 8 |
| w | C | Oct | 10 |
| w | C | Nov | 12 |
| w | C | Dec | 13 |
