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.
2 of 2 people found this helpful
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:
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:
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:
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:
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:
Now for the cross data source join version.
1) For this we join the Orders and Order Fill worksheets on Category, Product, and Week:
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:
5) And to build the final view all I had to do was remove the Order Fill% measure:
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!
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.