Nested Aggregation with IF statement HELP

[MattNatz]

Good afternoon,

I am struggling right now on trying to create a formula in which the below table I am able to do something like follows.  If a sum of total Full Customer Name sales (Company Sales/Total Sales) is greater than or equal to 80%.  Then take the Company Sales for customer by NDC and multiply that by a rebate %.  This then giving an estimate rebate amount if the customer reaches the threshold of 80%.  

Then I am also looking to take all Non-Company Sales and multiply that at the NDC level against the rebate % then aggregate it up to where the customer could see a Rebate Opportunity.  

Is there a simple way to do this?  I feel like I might be overcomplicating it and it could be all done in just IF statements.

Any help is greatly apprecaited.

Matt

[Chanty4u]

Hi 

Try this 

SUM(IF(

    SUM({<Full Customer Name=>} [Company Sales]) / SUM({<Full Customer Name=>} [Total Sales]) < 0.8, 

    [Non-Company Sales] * [Rebate %] * 0.01 * [NDC Sales],

    0

))