9 Replies Latest reply on Jun 21, 2018 1:41 PM by Deepak Rai

# Multiple Conditions

Hi there,

Criteria is total Category level should be more than \$1000 and Supplier level should be less than \$1000

If Category >1000 and Supplier <1000 then get sum of the PO order

Below is the simplified sample:

Category "a" can't be  since it has more than \$1000

Category "c" can;t be even though suppliers' less than \$1000 but total is less then \$1000 for category

Category "d" can't be since Category level is less than \$1000 even though supplier level meets our criteria

Category "b" is the one we are looking because total is \$1300 more than \$1000 and each supplier is less than 1000

Category "e" also meets our criteria

So, category "b" and "e" will be in my table with sum of PO orders and in the second table when i press "b" category it should bring me to company names and their balance.

Data should be fixed but it keeps changing when i filter to at least 100000 and bring suppliers...

categorysupplierPO order amount
a

zxy company

1100
babc company400
aasd company520
cert company650
csd company100
bzxy company300
bzxy company600
dsdf company800
ezxy company500
esd company600
• ###### 1. Re: Multiple Conditions

Have you tried creating two fixed calculations for total category and total supplier and then referencing those in your IF calculation?

[Total Category Order Amount]

{ FIXED [Category]:sum([Order Amount])}

[Total Supplier Order Amount]

{ FIXED [Supplier]:sum([Order Amount])}

IF [Total Category Order Amount]>1000

AND [Total Supplier Order Amount]<1000

THEN [Order Amount]

ELSE 0

END

• ###### 2. Re: Multiple Conditions

Two LOD calcs:

{ FIXED [Category] : SUM( if [PO Order Amount] > 1000 then 1 else 0 END ) }

{ FIXED [[supplier] : SUM( if [PO Orderr amount] < 1000 then 1 else 0 END ) }

For each row, if both calcs are 1 then you have a row you want to use.

If you can't get this to work, upload a sample workbook.

• ###### 3. Re: Multiple Conditions

You have a,b,c in this data because one of the supplier for a is less than 1000. Pl check screenshot and attached.

Thanks

Deepak

1 of 1 people found this helpful
• ###### 4. Re: Multiple Conditions

Hi Deepak,

Appreciate your response; however, i am still getting errors.

In your suggestion above,I don't want to include category "a" since zxy company has more than \$1000 so Category a's supplier is more than 1000.

I want to just see categories more than \$1000(total) with condition each supplier in that category not to exceed \$1000. So, in my example above i just want to see category "b" and "e".

Is there a way to sum up total suppliers for one category? e.g. sum of the total suppliers value for category "a"?

Thanks a lot

• ###### 5. Re: Multiple Conditions

Hi Jaime,

Appreciate your response; however, i am still getting errors.

I tried to apply your formula but it is still not working

I want to just see categories more than \$1000(total) with condition each supplier in that category not to exceed \$1000. So, in my example above i just want to see category "b" and "e".

Is there a way to sum up total suppliers for one category? e.g. sum of the total suppliers value for category "a"?

Thanks a lot,

Jane

• ###### 6. Re: Multiple Conditions

Here it is, Just Changed the Calculation to give u what u r looking for.

Thanks

Deepak

If it Helps, Pl mark it Helpful and CORRECT to Close Thread

1 of 1 people found this helpful
• ###### 7. Re: Multiple Conditions

Hi Jane,

If it Helped You, Can you Please Close This Old Thread To Help Someone looking for same answer by marking my Reply as CORRECt. The CORRECT Button is under my Reply in your this main Thread, Not in email

Thanks

Deepak

1 of 1 people found this helpful
• ###### 8. Re: Multiple Conditions

This is great! Thanks a lot! Much appreciate your help!

• ###### 9. Re: Multiple Conditions