Is there any way to place multiple groups (where some groups might contain members in other groups) on one sheet and get the correct sales values? For example, there may be two product groups (coffee & cold drinks) but a product (such as iced coffee) might be included in both groups. How can this be done to take into account sales for the product in both groups when placed on the same sheet for comparison?
I've attempted making two separate groups, a single group that includes two groups inside the group (which can't be done since one member can't be placed in more than one group), and also tried a calculated field IF-THEN statement.
Here is an example of the code for creating the group as a calculated field:
IF [Product]="French Roast" THEN "Coffee"
ELSEIF [Product]="Vanilla" THEN "Coffee"
ELSEIF [Product]="Hazelnut" THEN "Coffee"
ELSEIF [Product]="Iced Coffee" THEN "Coffee"
ELSEIF [Product]="Soda" THEN "Cold Drinks"
ELSEIF [Product]="Sports Drink" THEN "Cold Drinks"
ELSEIF [Product]="Iced Coffee" THEN "Cold Drinks"
END
Your help is greatly appreciated! Let me know if you need any clarification.
Thanks!
Hi Robert,
Thanks for the response. Unfortunately, this does still not solve my issue. The method you have suggested (I changed a couple things to fit my needs) provides the information split into 4 rows:
Using this you can come up with the correct sales number for each one, but I am looking for a more efficient method:
Sales of Coffee: 192,083 + 18,257 = 210,340
Sales of Cold Drinks: 16,857 + 18,257 = 35,114
I need it to display something like this (with the correct sales numbers):
As you can see the 'Sales of Cold Drinks" value exlcudes the values that overlap which account for the 18,257
Any other thoughts?
Thank you!!
