“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