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

[avinashelite]

Actually not  able to understand your  requirement , could you help to share the output your looking for then will suggest the solution 

[Learner]

When April month is selected than Rubber column must give following result:

A	10
B	15
C	20
D	25

When may is selected then:

A	15
B	20
C	25
D	30

These are plastics qty. which must be show in rubber column month wise..........

[vinieme12]

try below

alt(Sum({<Product={'Plastic'}>}Qty),Sum({<Product={'Rubber'}>}Qty))

[Learner]

Hi ,

I have applied your formula but it is giving qty. sum only for plastic. What i need is, i want this qty. total in Rubber column.............

regards,

Ashish

[vinieme12]

sorry , can you please provide some context again what are you exactly trying to achieve here,  

???? What i need is, i want this qty. total in Rubber column.............

using the sample, you provide the result is as per the expected out that was provided above 

When April month is selected than Rubber column must give following result:

A	10
B	15
C	20
D	25

When may is selected then:

A	15
B	20
C	25
D	30

[Learner]

I mean i want this april month total of 10,15,20,25 in Rubber (Product ) Column.

Below is the snapshot of what i want in Excel:

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

I need this output when April & Rubber is selected:

Above selection should give below result:( this is in excel)

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

regards,

Ashish

[vinieme12]

[Learner]

In dimension Product is not taken. I want product dimension also. 

After taking product dimension these 10,15, 20,25 should come in Rubber (Product) column in pivot table.

With regards,

Ashish

[vinieme12]

can you provide the complete use case!

you've explained what you need when Rubber is Selected and a Month is selected what about when nothing is selected ?

best to upload excel file with expected output of the required scenarios , makes it easier for members to understand