I've read through your post a couple of times now and I'm still confused. When you have a question like this, posting a packaged workbook with sample data is helpful to folks like me who would like to help.
Based on what you wrote, I think the simplest option is to create a single revenue calculated field that includes the calculations you posted, and returns the correct revenue value for each product. That way Tableau can appropriately generate the revenue total.
In order to make this easier to understand, I've pulled together a set of sample data and a workbook. I'd like to modify the first two worksheets to show Products 1 and 2 with the incremental revenue from Packages 1-3. As shown in the worksheets, Product 1 Bookings for 2011 should be 171,483 and for 2010-2011 should be 325,670. Product 2 should have 415,030 in 2011 and 814,983 for 2010-2011. I would like to remove Packages 1-3 from the portfolio view (sheets 1-2), and only have them represented as a portion of Products 1 and 2. Thanks!
Here's a workbook that does the calculation. I used the method I'd described above, though it gets a bit complicated for two reasons:
- Some data (the booking revenue for packages) needs to be counted multiple times. In Tableau, this generally calls for custom SQL to generate a union or cross-product, and/or table calculations.
- The chosen measure (Booking Revenue) is actually an aggregation of certain results for that measure across the chosen dimension (Product). Doing something like that in Tableau could call for reshaping data, or a table calculation
I chose to go with a table calc, called Booking Revenue (Revised) in the attached workbook. It begins with an IF statement for each product that requires a different calculation, then within that clause a table calc that does a sum that effectively filters for the specific Booking Revenue that is desired. The field is then set to calculate along Product for each Year. Here it is:
IF ATTR([Product]) = "Product 1" THEN
IF [Product] = "Product 1" THEN [Booking Revenue]
ELSEIF [Product] = "Package 1" THEN 0.2 * [Booking Revenue]
ELSEIF [Product]="Package 2" THEN 0.3*[Booking Revenue]
ELSEIF [Product]="Package 3" THEN 0.4*[Booking Revenue]
ELSEIF ATTR([Product]) = "Product 2" THEN
IF [Product] = "Product 2" THEN [Booking Revenue]
ELSEIF [Product] = "Package 1" THEN 0.8 * [Booking Revenue]
ELSEIF [Product]="Package 2" THEN 0.1*[Booking Revenue]
ELSEIF [Product]="Package 3" THEN 0.2*[Booking Revenue]
ELSEIF ATTR(LEFT([Product],7)) = "Package" THEN
[Booking Revenue per Product]
The Packages are given a revenue of Null and then Hidden from the results so the table calcs will work correctly.
Does this meet your needs?
PS: You can save your workbooks as a Packaged Workbook when posting them, the .twbx format is essentially a zip file with the .twb file and your data source(s).
Yes, that works great! Thank you.