Total of one product in another product line item wise

[Learner]

Dear All,

I am trying to develop app for some of my MIS data, as the data is sensitive  i have created sample data , which is as follows:

Year	Month	Product	Raw material	Qty.
2022	Apr-01	Plastic	A	10
2022	Apr-01	Plastic	B	15
2022	Apr-01	Plastic	C	20
2022	Apr-01	Plastic	D	25
2022	Apr-01	Rubber	D	30
2022	Apr-01	Rubber	E	35
2022	Apr-01	Rubber	F	40
2022	Apr-01	Rubber	G	45
2022	May-01	Plastic	A	15
2022	May-01	Plastic	B	20
2022	May-01	Plastic	C	25
2022	May-01	Plastic	D	30
2022	May-01	Rubber	D	35
2022	May-01	Rubber	E	40
2022	May-01	Rubber	F	45
2022	May-01	Rubber	G	50

My question is how can we total plastic product raw material wise in Rubber product. I have tried some formula but it does not give correct answer when i select month, below is the detail:

My formula is(in Pivot table, expression):

if(Product='Plastic', Sum(Qty.),if(Product='Rubber', sum( TOTAL <[Raw material]>{<Product={'Plastic'}>} Qty.),1))

When month is not selected it gives correct answer as follows:

Which is total of Plastic product raw material wise(A,B,C,D) for both month Apr & may.

But when i select Month than it gives wrong answer:

Please help me out for this problem, as i have tried many things but could not succeed.

Thanks in advance.

Regards,

Ashish

[Learner]

As i mentioned, my data is sensitive and hence i could not post complete details and hence i have created sample data. Actually i have some complex calculation in rubber column which i can achieve if i have plastic qty. in rubber column raw material wise. 

So what i want is when april & Rubber is selected it should show following:

Raw material	Product	Plastic	Rubber
A		10	10
B		15	15
C		20	20
D		25	25

When Plastic is then:

Raw material	Product	Plastic
A		10
B		15
C		20
D		25

When nothing is selected:

Raw material	Product	Plastic	Rubber
A		25	25
B		35	35
C		45	45
D		55	55

When nothing is selected at that time my formula is giving correct result, only when Month & Product is selected that time it does not give result as expected.........

regards,

Ashish

[vinieme12]

It's not about data, but perhaps you can post a sample that best represents  your dataset and examples and expected output that you are looking to achieve

At the moment the expected output still  matches what you've provided

No Selections

Column1 = Expression for Plastic

Column1 = Expression for Rubber

April and  Rubber

Column1 = Expression for Plastic

Column1 = Expression for Rubber

[Learner]

Can we achieve this with single expression and product as column?

As i have 13 products, so i will have to write 13 separate expressions.

Regards,

Ashish

[Learner]

Dear All,

Please help me in the above problem as it will be very useful for me........

with regards,

Ashish