10 Replies Latest reply on Jan 19, 2014 5:08 PM by Derek Holland

    Can anyone help with a Tableau “averaging” question?

    Derek Holland

      “Help needed for a Tableau “averaging” question.


      Attached are a packaged workbook, a screenshot image and Excel spreadsheet,  which I hope can help explain an roll-up issue we are encountering when building aggregate reports.


      We have a data source for a customer who may place one or more orders and each order may have one or more line items.
      (See Sheet1 in the attached spreadsheet – the sql statement returns the rows shown).


      For any given order, there will be an original and a revised charge rate which is then used in calculations against the cost of each line item to arrive at the final charges.


      Naming this data source “Customer_Example1”, we can present the data in a simple text table in tableau.


      Reporting at a “Line Item” level we will see something like “Table1” in the attached screenshot image  “CustomerExample.png”.

      The table has a grand total row and the figures highlighted in yellow are what we would expect.


      If we use the same data source, Customer_Example1, but report at a “Order” level, see Table 2,

      the averages shown in the total row in the red boxes still show the averages at a “Line Item” level – understood but misleading on this table.


      If we create a second data source, Customer_Example2, and group the results by
      Customer and Order, (see sheet2 in the spreadsheet) we arrive Table3 as shown
      in the CusomerExample image – where we see the expected averages in the green boxes.


      My question is – if we use data source Customer_Example1, with details at the
      “Line item” level – is there a way to create a Table3  where we can show
      averages at the “Order” level without having to use a second data source
      rolling up to the Order level?


      I hope that makes sense but if it needs any further clarification please let me know.


      Thanks in advance for any help that can be offered