5 Replies Latest reply on Aug 5, 2015 6:05 AM by Ian Burdin

# Average order value calc field

Hi all

I have some basic campaign data which is all fine, apart from the calculated fields I have created.

The data has orders, revenue, visits and quotes. I have created 4 fields:

Average Order Value (Revenue/Orders)

Conversion (Orders/Visits)

Quotes to visits (quotes/visits)

I want to split these metrics out by channel (social media, display, aggregator etc) and by device type (desktop, tablet etc)

The data has integrated into Tableau fine, but when I introduce a channel and device type split, the calculations are incorrect.

For example using a simple calc in excel...for the aggregator channel the numbers are:

Orders: 14,689

Revenue: £2,753,250

AOV should equal £187.43 but as per the attached workbook, the answer I'm receiving is £179.11

Similarly, conversion should equal 27.8% but in the workbook the answer is 28.9%.

Any help gratefully appreciated!

• ###### 1. Re: Average order value calc field

Hi Ian,

In Tableau unless you need a row level calculation, you need t aggregate your measure values. So if you wrap both the calcs AOV and Conversion under a SUM(), you should get correct results.

AOV: (sum([Revenue])/sum([Orders]))

Conversion: sum([Orders])/sum([Visits])

• ###### 2. Re: Average order value calc field

Hi Ian,

change AOV calc as sum(revenue)/sum(orders)

drag this into measure area. now get 187.43

thanks

sankar

1 of 1 people found this helpful
• ###### 3. Re: Average order value calc field

Brilliant many thanks

• ###### 4. Re: Average order value calc field

Hi Ian,

that's a logical issue here. Your formular tells tableau to make 'x' / 'y' and then take 'AVG' of the results. But what you want, is the weighted average. That will be the 'SUM of x' / 'SUM of y'.

Looks like that:

Done. The result will already be the weighted average:

I put that in your *.twbx-file and attached it.