2 Replies Latest reply on Sep 6, 2018 6:45 AM by Roberto Nieto

# Running Total Average and LOD

Hi everybody

I am trying to get a Running Total Average of Sales per day using the following dataset:

 Date Order ID Sales 01/01/2018 A0001 1 01/01/2018 A0002 5 01/01/2018 A0003 3 01/01/2018 A0004 4 02/01/2018 A0005 3 02/01/2018 A0006 1 02/01/2018 A0007 1 02/01/2018 A0008 0 02/01/2018 A0009 25 02/01/2018 A0010 200 02/01/2018 A0011 250 03/01/2018 A0012 3 03/01/2018 A0013 6 03/01/2018 A0014 1 03/01/2018 A0015 1 Average 33.6

This is what I would like to achieve:

 Date Order ID Sales Running Total Sum Running Sum of Records Running Avg (LOD OrderID) 01/01/2018 A0001 1 1 1 1.00 01/01/2018 A0002 5 6 2 3.00 01/01/2018 A0003 3 9 3 3.00 01/01/2018 A0004 4 13 4 3.25 02/01/2018 A0005 3 16 5 3.20 02/01/2018 A0006 1 17 6 2.83 02/01/2018 A0007 1 18 7 2.57 02/01/2018 A0008 0 18 8 2.25 02/01/2018 A0009 25 43 9 4.78 02/01/2018 A0010 200 243 10 24.30 02/01/2018 A0011 250 493 11 44.82 03/01/2018 A0012 3 496 12 41.33 03/01/2018 A0013 6 502 13 38.62 03/01/2018 A0014 1 503 14 35.93 03/01/2018 A0015 1 504 15 33.60

However as I don't include OrderID on the pill's mark  Tableau calculates average based the aggregation by date instead of the lowest level of detail:

 Date Average per day Running Sum of Records Running Total Sum Running Avg (LOD Date) 01/01/2018 3.25 1 3.25 3.25 02/01/2018 68.57 2 71.82 35.91 03/01/2018 3 3 74.82 24.94

I tried the following calculated field however it does not seem to work:

{ INCLUDE [Order ID]: AVG([Sales])}

I attached example file.

Many thanks

Roberto

• ###### 1. Re: Running Total Average and LOD

hi Roberto,

So I think you can do it like this

btw you can nest all these into a single formula, but I've split out to help explain

Create the running sum of sales

[Running Sum Sales]

RUNNING_SUM(SUM([Sales]))

Create running sum for Order Count

[Running Sum Order Count]

RUNNING_SUM(COUNTD([Order ID]))

and then you can just divide one by the other

[Average Order Running Sum]

[Running Sum Sales]

/

[Running Sum Order Count]

Hope that helps

• ###### 2. Re: Running Total Average and LOD

Many ThanksSimon Runc!

Just tried and works like a charm.