4 Replies Latest reply on Feb 20, 2012 3:25 PM by brian.giracca

    Using Calculated Fields and Percentages

      I manage a product portfolio that includes about 10 products.  Two of the products (Product 1 and Product 2) generate their own revenue, but also receive a percentage of revenue generated from Packages, which are also listed as Products.  I have been able to use a Calculated Field to visualize how each of these 2 products generates their revenue, using these calculations:

       

      IF [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 [Product]="Product 1" THEN [Booking Revenue]END

       

      IF [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 [Product]="Product 2" THEN [Booking Revenue]END

       

      Now I am trying to create a simple chart that shows the revenue for all 10 products, including Product 1 and Product 2 (with their incremental Package revenue included), and shows the portfolio total revenue at the bottom.  The part that’s most confusing here is how to group the components of Product 1 and Product 2 when they each get a percentage of Packages 1, 2 and 3.

        • 1. Re: Using Calculated Fields and Percentages
          Jonathan Drummey

          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.

           

          Jonathan

          • 2. Re: Using Calculated Fields and Percentages

            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!

            • 3. Re: Using Calculated Fields and Percentages
              Jonathan Drummey

              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

                  WINDOW_SUM(SUM(

                      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]

                      END

                  ))

              ELSEIF ATTR([Product]) = "Product 2" THEN

                  WINDOW_SUM(SUM(

                      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]

                      END

                  ))

              ELSEIF ATTR(LEFT([Product],7)) = "Package" THEN

                  Null

              ELSE

                  [Booking Revenue per Product]

              END

               

              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?

               

              Jonathan

               

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

              • 4. Re: Using Calculated Fields and Percentages

                Yes, that works great!  Thank you.