3 Replies Latest reply on Oct 4, 2016 12:32 PM by Nick Pittman

    Weighted-average subtotals using blended data source

    Nick Pittman

      I'm having trouble with the subtotals when using a blended data source. I've got two basic tables- one with the number of orders and one with the anticipated order fill percentage. I'm multiplying the number of orders times the anticpated order fill % to calculate the number of orders filled.

       

      The problem is, because I'm performing a calculation across data sources, Tableau is aggregating the field prior to performing the calculation. As a result I'm unable to subtotal the weighted average. Also, Tableau wont perform the second-pass aggregation on the secondary data source.

       

      See the image the image below and the attached workbook for a better idea of what I need to accomplish.

       

      Does anyone have any workarounds for this?

       

      Picture3.png

        • 1. Re: Weighted-average subtotals using blended data source
          Jonathan Drummey

          Hi Nick,

           

          Is there any chance you can set up your data source to do a join instead of a blend? Alternatively, can you upgrade to Tableau 10 and use cross data source joins? Those two would make the desired results easier to achieve than trying to do this with data blending.

           

          Jonathan

          • 2. Re: Weighted-average subtotals using blended data source
            Jonathan Drummey

            I decided to put together two examples:

             

            1) This version uses Tableau's data blending, an increased level of detail on the subtotal so Tableau's aggregation after the blend stays at the finest grain necessary, table calculations to appropriately recompute each measure to deal with the increased level of detail, a table calc filter to get rid of extra marks in the detail rows and turns off Aggregate Marks to get the subtotals to draw properly:

             

            Screen Shot 2016-10-03 at 10.38.08 AM.png

             

            2) Using Tableau v10's cross data source (xDS) join feature I joined the two Excel files together and created one record-level and one aggregate calculation:

             

            Screen Shot 2016-10-03 at 10.17.21 AM.png

             

            I believe most anyone would agree that the second view is easier to figure out. For now I'm going to quickly go through the steps, I'll have a longer explanation later this week.

             

            Here's how I built the blending solution.

             

            1) Increased the level of detail for the subtotals by adding copies of the Category and Product dimensions from the primary (Orders) source. This makes Tableau draw extra marks in the subtotal:

            Screen Shot 2016-10-03 at 10.37.17 AM.png

            2) Created a # of Orders calc with the formula IF FIRST()==0 THEN WINDOW_SUM(SUM([Orders])) END and set the Compute Using to an Advanced... on Category (copy) and Product (copy). This uses the increased level of detail technique from http://drawingwithnumbers.artisart.org/customizing-grand-totals-part-2/.

            3) Created a # Orders Filled calc with the formula IF FIRST()==0 THEN WINDOW_SUM([Orders Filled Calculation]) END with the same Compute Using settings.

            4) Created a Weighted Order Fill % calc with the formula [# Orders Filled] / [# of Orders]. In the view this inherits the Compute Usings of the two calcs and returns the desired results:

            Screen Shot 2016-10-03 at 10.37.45 AM.png

             

            5) Now to build the final view. The first step is to duplicate the workout worksheet.

            6) Then Ctrl+Drag a copy of the # of Orders pill from Measure Values onto the Filters Shelf and set it to Filter for non-Null values. (This has a performance gain in v9.x and earlier because Tableau isn't drawing extra Null marks, I'm not sure if it does in Tableau v10 or not).

            7) Turn Analysis->Stack Marks->Off to get the Subtotals to stop stacking the marks:

             

            Screen Shot 2016-10-03 at 10.38.08 AM.png

             

            Now for the cross data source join version.

             

            1) For this we join the Orders and Order Fill worksheets on Category, Product, and Week:

             

            Screen Shot 2016-10-03 at 10.40.56 AM.png

             

            I chose to use a left join to make sure I wasn't losing any values, and validated that I wasn't getting any replicated values from the join.

             

            2) Build a # Orders Filled calc with the formula [Orders]*[Order Fill%]. This is a record-level evaluation so it is effectively weighting each Category/Product/Week combination.

             

            3) Build a Weighted Order Fill % calc with the formula SUM([# Orders Filled]) / SUM([Orders]). This is a regular aggregate calculation so it'll aggregate the record-level values to the desired level of detail (Product/Category in the detail rows, Category in the subtotals).

             

            4) Here's the workout view:

             

            Screen Shot 2016-10-03 at 10.45.42 AM.png

             

            5) And to build the final view all I had to do was remove the Order Fill% measure:

             

            Screen Shot 2016-10-03 at 10.45.49 AM.png

             

            If you were able to get both the Orders and Order Fill data into the same Excel workbook you could do the join in Tableau v9.3 and earlier and build these same calculations.

             

            I've attached both the v9.3 (blend only) and v10.0 (blend and xDS join) workbooks. If you have any questions let me know!

             

            Jonathan

            2 of 2 people found this helpful
            • 3. Re: Weighted-average subtotals using blended data source
              Nick Pittman

              Perfect. It took me while, but I was able to reconstruct your blending technique to achieve the desired subtotals. Ultimately, I'm going to work toward getting the data joined on the DB side, but this was extremely helpful. Cant thank you enough.

               

              Nick