I have a custom report type for Accounts with and without Opportunities and I'm filtering on Closed-Won. But I can't seem to write a formula or structure the report in a way that accurately calculates the percent of accounts with closed-won opportunities. Many accounts have multiple closed-won opportunities, so formulas that use "Won" return an inflated percent.
Any suggestions for how to set this up so it calculates based on the number of accounts with a won opportunity rather than the number of won opportunities?
1 个回答
There may be other ways to achieve this, but here's how I'd handle it:
1) Create a roll-up sumary field on the account which counts the number of Closed-Won opportunities on the account.
2) Create your report as a regular Accounts report (no need for the with/without one now) and pull in this new field
3) Use a bucket field to return Yes if the number of won opps is greater than zero and No if it's zero
4) Then summarise on your bucket field
This may seem like a long way round but the advantage in creating the roll-up summary is that you can then use this later to drive additional insight (How many of my accounts have over 10 won opps? What is the average number of won opps by account owner? etc...).
