4 Replies Latest reply on Apr 22, 2016 8:40 AM by Travis Hauck

    Grouping Data by multiple Dimensions from multiple tables

    Travis Hauck

      Only my second time posting here and I am under pretty strict sharing rules so it's difficult for me to upload workbooks for review, so I'll do my best to clearly layout the issue.

       

      I am trying to create a report that will give me the percentage of business that is currently under contract vs. once-off orders.  However, the contracts are defined differently and do not always encompass every part ordered by a customer.  I have three tables within an Excel source data workbook to work from:

      1. Order table: all order data for every order booked within a specified time period
      2. Contract table: a list of customers and the details of their contracts.  Of specific concern is whether the contract is a Master Supply Agreement (MSA), which covers all parts for all locations for a customer group; Supply Agreement (SA), which covers all parts for a specific site, but not the entire customer group; and a Pricing Agreement (PA), which covers only named parts for a specific customer site.
      3. Pricing agreement matrix: for those customers under a PA, this is the list of parts that are covered by that PA.

       

      What I need to output is the total of orders, categorized by Non-contract vs. contract.  I have been able to do so by doing a left join between the Order and Contract Table, then grouping all contract types together from the Contract Table and applying them to the color card.  The below Viz shows this with the top panes being a table calculation of <% of total (cell)> and the bottom panes being sum of order values.  The problem is that by doing this, I am including 100% of parts that are under PA, when need to create a group (formula, parameter, bin...?) that groups all MSA and SA customer orders together, plus all parts covered by PA.

       

      Now, I could do this in the Excel source file with some vlookups, but I'm really trying to keep the data source simple and do the calculating in Tableau - for efficiency's sake and for sake of learning as much as I can about Tableau's capabilities.  Thanks for the help!