Insights How-To Multi-Select Table Fields

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:

1. 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

1. 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:

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).

Now the actual revenue is $4100; because we eliminated unwanted duplicates.