User created a saved search base on Opportunity. The Result is grouped by Forecast Type. User would like to get the sum of the Projected Total for each Forecast Type. The sum should only get the distinct value of the projected total amount. Example data below:
Opportunity # | Forecast Type | Projected Amount |
1 | Most Likely | 2,000.00 |
2 | Most Likely | 745.99 |
3 | Most Likely | 799.00 |
4 | Most Likely | 50.00 |
5 | Most Likely | 799.00 |
6 | Most Likely | 745.99 |
Sum of Projected Total should only be 3,594.99 instead of 5,139.98.
To achieve this, perform the following steps:
1. Navigate to Lists > Search > Saved Searches > New.
2. Select Opportunity.
3. In the Criteria tab > Standard subtab, add Filters to narrow down result.
4. In the Results tab > Columns subtab, use the Fields, Summary Type and Formula below:
Forecast Type | Group
Formula (Currency) | Sum | SUM(distinct{projectedamount})
5. Click Preview or Save & Run.
Is there a way to do this same sum but with a partition to take the distinct amount within each category but to include all categories. In the case that there are identical values in different categories, I'd like to include those.
ReplyDeleteIn the example above, for instance, If we also included "Not Likely" and there was also a 799.00 value in that group, I'd like the 799.00 to be counted in both categories. I've tried to SUM OVER PARTITION, but it doesn't seem to work. I've tried the sum /* comment */ route as well. I think it's likely a syntax issue.
I thought about SUM(distinct TO_VALUE(CONCAT({amount},{order.numer}/100000)) or something similar, to make each value unique and then ignore the small differences with ROUND.
Thoughts?