Drew, it would be great if you could include 20 or so lines of data to work with.
How about instead of an Ad Hoc Group (paper clip icon), a calculated field like:
Product Group Type
CASE [Product Group] WHEN 1 THEN "Unique" WHEN 2 THEN "Secondary" WHEN 3 THEN "Unique" WHEN 4 THEN "Unique" WHEN 5 THEN "Unique" WHEN 6 THEN "Secondary" WHEN 7 THEN "Secondary" WHEN 8 THEN "Secondary" END
This way you have it as a calculated field, which is more versatile than an ad hoc group.
Then you can have calculated fields like:
Unique Customer ID
IF [Product Group Type]="Unique" THEN [Customer ID] END
SUM([Total Amount])/COUNTD([Unique Customer ID])
In the attached you can see that there are 5 "Unique Customer ID", with an overall Total amount of 457. So the Yield is 457 / 5 = 91.4
Depending on your data and business logic, you might want a COUNT() instead of a COUNTD() in the Yield formula.
yield.twbx 24.2 KB
Thanks Joe, I did that and it worked great.
Thanks, helped me too!
So just for my info, should I stop using ad-hoc group and start creating my group using rules? Sometimes it is harder when you have a long list of elements, is it something plan: to be able to use ad-hoc group in calculated field?
It is something Tableau is aware of, but there is no word yet on when ad hoc groups will be able to be referenced in calculated fields.