Aggregate Formulas for Cumulative Conditional Commissions

Let’s step up the complexity a little bit! What if you want to award conditional commission rates based on the cumulative level of sales and not an individual sales are transactions? In that case, we need to turn to aggregate formulas. (Watch our course on those already if you haven’t)

Let’s say you give 3% commission on total sales under $50,000 and 5% on the next $50,000 in sales, and 7% on anything above that? You cannot run that calculation in a pivot formula or a normal formula column.

In an aggregate formula though, you can spell it all out with the following code:

if(sum("Deals"."Deal Size") < 0,0,if(sum("Deals"."Deal Size") < 50000,.03*sum("Deals"."Deal Size"),if(sum("Deals"."Deal Size") < 100000,1500+.05*(sum("Deals"."Deal Size")-50000),4000+.07*(sum("Deals"."Deal Size")-100000))))
Back to Lesson