ShowAll Questionssorted byDate Posted
ahassler

# Opportunity Report Formula If This Owner Then Quota is 1,000,000

I'm trying to write a formula in an opportunity report that would list the quota for each sales person. So basically: If Opportunity Owner is Bridget then \$1 milllion, if Opportunity Owner is Andy then \$2 million, if Opportunity Owner Bill then \$5 million. We don't use forcasts or other quota options native to SFDC so this is just a one time use formula in which I basically saying the report I want to list the quota for each person... Any help would greatly be appreciated!

Jeff May

Here's a shot: in a formula field (currency):

CASE( \$User.Username, "Joe Smith",1000,  "Ray Jay", 5000, "Bill Jones", 10000, 0)

Jeff May

If you are not using SFDC Quotas, the Answer will depend on where you are storing a user's quota.  If it is on the User record, you can use a User report -- no need for an Opportunity report.

ahassler
Sorry, I have to use the opportunity report becuase I am pulling in other details to create a table for the home page dashboard. I don't have a need to store the data either so I hadn't planned on created a custom field, but I would be open to it if necessary, but I would still need a formula and not sure if I should put it on the user page or the opportunity page as I'll need it to be accessible by the opportunity report. Thoughts?
Jeff May

The data model that seems to make the most sense would be to add the Quota amount on the User record.

To get that on an Opportunity, you can create a Lookup(User) field, for example "OwnerRecord".  A Opportunity trigger would just set the OwnerRecord field to the value of the OwnerId field.   You will then have access to the User record fields (including Quota) from the Opportunity.

ahassler
I'm more having trouble with the if then formula. So for example, Joe Smith is 1,000,00, Ray Jay is 5,000,000, Bill Jones is 10,00,000 so I've tried these formulas:

IF(IS(Username, "Joe Smith")), ROUND (1000000, 2), IF(IS(Username, "Ray Jay")), ROUND (5000000, 2), IF(IS(Username, "Bill Jones")), ROUND (10000000, 2)

IF(CONTAINS(Username, "Joe Smith")), ROUND (1000000, 2), IF(CONTAINS(Username, "Ray Jay")), ROUND (5000000, 2), IF(CONTAINS(Username, "Bill Jones")), ROUND (10000000, 2)

What am I doing wrong on the formula side?

FYI We're on professional edition so we don't have workflows and triggers.
Jeff May

Here's a shot: in a formula field (currency):

CASE( \$User.Username, "Joe Smith",1000,  "Ray Jay", 5000, "Bill Jones", 10000, 0)

This was selected as the best answer
ahassler

I get no errors on the formula, however when I ran it and checked the field in each person's user profile, everyone's quotas come out as \$0.00 dollars. Thoughts?

Jeff May

The \$User.Username is based on the current user.  If you are running the report, that would be you.  Your formula would need to reference a User field on the record if you want to run quota reports for other Users.

Rizaz
Hi people I know this is an old post But I need help something related to this. I want to create a formula field(currency) in the opportunity object that is related to close date which can give me a chance to use this field as opportunity quota.Example the logic is as follow: Opportunity Quota = case (the closedate is 31.01.2017, 2091391902(quota) , 28.02.2017, 10213989018). I don't know if I explain clearly or not