Insights - How to work with Multi-Select Table Fields
Updated 2 years ago
by
Elena Yurkova
Multi-select table fields
Example: 'Products' is a multi-select field. Let's say, a potential customer is interested in Product A and/or Product B. A Sales rep has checked these 2 products when an opportunity was created. However, when this opportunity was closed ($1000), the revenue field is the total for this opportunity. Therefore, it is impossible to know how much of the $1000 is related to Product A and how much to the Product B.
If you create a widget to know your total revenue by Product, the opportunity from above will be calculated twice; $1000 for Product A and $1000 for Product B. Sometimes this is the expected result, as both products contributed to the win of this opportunity, but in most cases you would like to avoid duplicates caused by multi-select fields.
How to Avoid Duplicates While Working With Multi-Select Fields:
- To minimize impact of multi-select fields, we've separated them from other fields. Watch out when you add these fields to a dashboard as they could potentially be multi-selected depending on the business processes:
- Account Manager for Company/Individuals
- Category for Company/Individuals and Contacts
- Product/Services for Opportunities and Customer Service
- Categories for Opportunities and Customer Service
- Table User-Defined fields for all modules
- To get correct results, use a multi-pass formula to eliminate duplicates created due to multi-selection. Before applying SUM, you GROUP BY by record identification and apply AVERAGE aggregation for each record.
How the Multi-Pass Formula Works:
At first glance, it's hard to understand the logic behind this formula. Let's understand the details by looking at the example.
In the example below, we have four opportunities with the total revenue $4100:
- Opportunity 1 (Record Identification = 1) has revenue $1000 (Actual Opportunity Revenue = 1000) and two products associated with this opportunity (Product A and B) and
- Opportunity 2 has revenue $2000 and one product associated
- Opportunity 3 has revenue $1000 and one product associated
- Opportunity 4 has revenue $100 and free products associated (Product A, B, C, and D)
A table view of these opportunities if you include a Product field, will be:
Record Identification | Product | Revenue (without multi-pass) |
1 | A | 1000 |
1 | B | 1000 |
2 | A | 2000 |
3 | A | 1000 |
4 | A | 100 |
4 | B | 100 |
4 | C | 100 |
Total Revenue (SUM) | 5300 |
If you apply SUM to these records, the total will be $5300 instead of the actual $4100.
Now, apply the multi-pass formula, SUM([Identification],[Average Revenue]). We are grouping by each ID, meaning if there are no duplicates, the average value will be the same as we are dividing by 1. If we have duplicates, the average value will divide the total by the number of duplicates (i.e. if the the same id repeats twice, divide by 2, if three times, divide by 3).
Group by Record Identification | Average Revenue by ID | Revenue with multi-pass |
1 | 1000 =(1000+1000)/2 | 1000 |
2 | 2000 =2000/1 | 2000 |
3 | 1000 =1000/1 | 1000 |
4 | 100 =(100+100+100)/3 | 100 |
Total Revenue | 4100 |
Now the actual revenue is $4100, because we eliminated unwanted duplicates.
