how to calculate the sum of a calculated sum

[ahidu1030]

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

[sunny_talwar]

What happens if you add Month to the Aggr dimension

Sum(Aggr(Sum([Delivery Quantity])*[copper weight / part], [Supplier],[Part], Month))

[rupamjyotidas]

If your dimensions in Pivot table is Supplier, Part and month

Then only this expression should work 'Sum([Delivery Quantity])*[copper weight / part]'.

If not working than I am missing something.

[ahidu1030]

This gives me only when I select a certain part. Because each part has different copper weight and silver weight, it isn't able to give me a total.

[ahidu1030]

Till doesn't give me the right answer...same result as my function above.

[sunny_talwar]

Based on the data you have provided above, what is the expected output you are looking to get?