Calculate window average in tableau

1
votes

I've got a data set that contains several tables: orders, order_items, and billings. Orders and Billings will have one row per orderID, but order_items could have multiple rows per orderID. OrderIDs can belong to one of 4 locations.

I'm trying to calculate, for each location, what the average billing total is using Tableau. However, when I do an avg(billing total) across locations, it looks like Tableau first adds the value of billing totals across all rows, so if one order has multiple order_item rows it will add the billing total together to get the "total" for that orderID -- then it takes the average of those.

Is there a way to have Tableau calculate an average across orderIDs without adding sub-fields together? Or to tell it specifically to aggregate at the orderID level, without adding orderID to the row/column shelf?

3
aggregate-functionstableau-api
I believe that the best solution, in your case, is to generate a new field with the billing value divided per item. I.e., if you have 4 items in your orderID, divide the value of the billing by 4. It's easily achievable through a custom SQL – Inox

3 Answers

0
votes

I know that this is an old question, but this is a problem that is now easily solvable with LOD expressions. This is more information on LOD's.

Without seeing your data or the workbook I would think that it will look something like this:

    {FIXED [orderID] : AVG([total billing]}
0
votes

Edit your table Calculation--> select advance setting take in Location OrderID in this order click ok at level Drop Down Select Location at restarting Every OrderID

or you could use a new calculated field : {Fixed [OrderID], [Location] : AVG(Order)

if possible a sample image with dummy data of what you want to achieve with just 3-5 rows would help

0
votes

Let's suppose your data is like

enter image description here

In this using this calculated field may help exactly as you desire

{FIXED [Location]: AVG({FIXED [Order Id] : AVG([Value])})}

Check

enter image description here

Method-2

if you don't want to include order_id in view you may also use this calc

{INCLUDE [Order Id] : avg([Value])}

See

enter image description here