9 Replies Latest reply on Nov 8, 2016 7:31 AM by alex.apolloni.0

# how do you take orders and group by customer for calculations?

Hello.

My data table looks like so:

[Date Of Order]  [Customer ID]  [Order ID]  [Product ID]  [Amount]

.

.

Row N

How do I make a calculated field so that i can say, "give me the average Order amount by customer" over time.

In SQL or DAX you could just GroupBy (or Summarize) by Date - Customer ID   - SUM(Amount)

And then average.....onto a date.

Many Thanks,

Alex

Message was edited by: alex apolloni added  example workbook

• ###### 1. Re: how do you take orders and group by customer for calculations?

Hi ,

Just drag Date,CustomerID,Amount into Rows shelf and convert Amount Measure aggregation to Average and convert it into discrete :

• ###### 2. Re: how do you take orders and group by customer for calculations?

Hi.

Try this:

{Fixed [Customer ID]: Avg([Amount] )}

If you need this average vary along time, try this one:

{Fixed [Customer ID],[Date Of Order]: Avg([Amount] )}

• ###### 3. Re: how do you take orders and group by customer for calculations?

Hi Luciano,

Thanks for the response but it's not quite adding up for me...

I added a Workbook...

For example,

if I have

[customer][amount]

AL          1

BOB          3

Chuck          7

Chuck          13

If I do a median of the 3 customers.

AL     1

BOB     3

CHUCK     20

I should get 3

but with the calculation, i get 6

And if I do an average of the 3 customers,

i should do (1+3+20)/3  = 8

but using {FIXED [Customer ID]:AVG([Amount])}

I get 14

Thanks,

Alex

• ###### 4. Re: how do you take orders and group by customer for calculations?

I don't have tableau 10.

Post some data please.

• ###### 5. Re: how do you take orders and group by customer for calculations?

D'oh.  hadn't thought of that.

XLS  of data is attached.

avg of customer orders=

sum([Amount])/COUNTD([Customer ID])

FIXED of AVERAGE=

{FIXED [Customer ID]:AVG([Amount])}

MEDIAN of FIXED customer orders=

MEDIAN({ FIXED [Customer ID]:SUM([Amount])})

• ###### 7. Re: how do you take orders and group by customer for calculations?

Is this what you are looking for :

• ###### 8. Re: how do you take orders and group by customer for calculations?

Hi Hima,

Sorry, no.  Look at Re: how do you take orders and group by customer for calculations?

I tried to generalize my question but maybe I made it to general originally.

I want to graph the data...

but I want to be able to run other calculations on it as well....like, for example ,the MEDIAN function.

In what I've show, the "avg of customer orders:8" is correct.  (but that is a calculation of

avg of customer orders=

sum([Amount])/COUNTD([Customer ID])

I need to be able to put itself into a calcuaton that returns a list of VALUES so that I can run MEDIAN on it.

• ###### 9. Re: how do you take orders and group by customer for calculations?

The thing  I was looking for was "LOD Expressions".

This answers my question:   Re: Median for calculated metric

And the Tableau videos here help:  Introduction to LOD Expressions