1 Reply Latest reply on Aug 1, 2017 2:58 PM by Deepak Rai

# Calculation: Sales Brand Performance of Overall Portfolio  - multiple criteria

Hi All, I'm attempting to write a calculation that would automate some work that is being done and save a ton of time!

Here is a background of the issue.

Here at my job we accrue sales for potential sales payouts based on multiple performance criteria.

We have a Target 1 Quota that is indiscriminate of the brand. -Once reached they get paid 1% of their annual Sales We have a Target 2 Quota that is based on brand performance. -We have quotas for 9 Brands however, not everyone is given a quota for that particular brand.

My first calculation to determine Target 1 Potential 1% Payout is

"IF (([% Quota YTD]>=1) THEN ((SUM([YTD Sales])*0.01)"

This works fine but the tricky part is when I'm trying to create Target 2 Potential Payout.

The logic is the following: There are 7 Brands that are given a quota Each individual may or may not be given a quota for all brands.

You may have an individual with 2 or 3 brand quotas, despite have sales information for those brands not given a quota.

My current Calculation contains two different dimensions.

Brand YTD Sales "IF [Brand-New3] = 'Name1' OR

[Brand-New3] = 'Name2' OR

[Brand-New3] = 'Name3' OR

[Brand-New3] = 'Name4' OR

[Brand-New3] = 'Name5' OR

[Brand-New3] = 'Name6' OR

[Brand-New3] = 'Name7' OR

[Brand-New3] = 'Name7' OR

[Brand-New3] = 'Name8' OR

[Brand-New3] = 'Name9' THEN ([YTD Sales]) END"

This works great; however, those individuals that have sold Brand 'Name7' but who were not given a quota still get summed up! Would a window sum be appropriate in this scenario?

Ok and finally the Target 2 Potential Payout:

"IF ([%Quota YTD Level 2]>1) THEN ((([% Brand Quota YTD- 2017]0.01)([Potential 1% Payout])) END"

This seems like it should not be as difficult as it seems. I have thought of many other ways to go about this.

Create calculation for each individual Brand for example:

Brand Name1

"IF [Brand-New3] = 'Name1' THEN [YTD Sales]) END"

The only issue with this again is it gives me sales for brands that individuals do not have quotas for.

I'm thinking a LOD expression?

Anyway I hope I explained myself clearly, I could really use the help!

• ###### 1. Re: Calculation: Sales Brand Performance of Overall Portfolio  - multiple criteria

Can you attach workbook?