Good morning,
I am working with a sales data table for which I have created the following corresponding Pivot Table fields:
1. customer (company name)
2. deal stage (Lead, Prospect, Qualification, Evaluation, On Hold, POC, Negotiations/Legal, Closed Won, Closed Lost)
3. deal name (internal name for a deal)
In the source data, there can be multiple deals - "deal names" - associated with a single customer/company. I need to accurately sum customers/companies, but those that have multiple deals associated with them are skewing the totals for customer/company (see below).
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Customers[/TD]
[TD]Total by Stage[/TD]
[/TR]
[TR]
[TD]Customer ABC[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Grand total[/TD]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]
Customer ABC should count as 1 company with 2 deals. My current solution is overstating how many customers/companies there are. I am looking for a "reductionist" solution that will group or calculate the sum of this field as 1.
I have looked everywhere for a solution to this seemingly simple calculation problem, and I'm out of ideas, and hope someone can help!
Thanks!
I am working with a sales data table for which I have created the following corresponding Pivot Table fields:
1. customer (company name)
2. deal stage (Lead, Prospect, Qualification, Evaluation, On Hold, POC, Negotiations/Legal, Closed Won, Closed Lost)
3. deal name (internal name for a deal)
In the source data, there can be multiple deals - "deal names" - associated with a single customer/company. I need to accurately sum customers/companies, but those that have multiple deals associated with them are skewing the totals for customer/company (see below).
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Customers[/TD]
[TD]Total by Stage[/TD]
[/TR]
[TR]
[TD]Customer ABC[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
Negotiations/Legal
[/TD][TD]2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]
Dealname 1
[/TD][TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]
Dealname 2
[/TD][TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Grand total[/TD]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]
Customer ABC should count as 1 company with 2 deals. My current solution is overstating how many customers/companies there are. I am looking for a "reductionist" solution that will group or calculate the sum of this field as 1.
I have looked everywhere for a solution to this seemingly simple calculation problem, and I'm out of ideas, and hope someone can help!
Thanks!