11 Replies Latest reply on Jun 22, 2018 6:15 PM by beckie.rohr.0

# Average vs. Average per Customer

What is the difference between getting the average of all customer sales vs. using this function (see below) to get the average sales per customer?

AVG({INCLUDE [Customer Name] : SUM([Sales])})

Thank you,

B

• ###### 1. Re: Average vs. Average per Customer

Hi Becky

In Simple Terms, See This Way

If You want To Get Average Of All Customers Just Use This

{AVG(Sales)} will Return Average for Entire Dataset

{Fixed Customer:AVG(Sales)}  Will Return Avg Sales Per Customer . You can Use {INCLUDE Customer:AVG(Sales)}  also

Result will Be Same

Now If You Drag These to Rows  Or to Labels you would Get

SUM{INCLUDE Customer:AVG(Sales)}    -----(1)

Or You may Change it to

AVG{INCLUDE Customer:AVG(Sales)} ------(2)

Both Equation (1) and (2) as above will Give you Same Results. Aggregation Of SUM OR AVG is meaningless. They will Give you Average Sales Per Customer Aggregated. It means If a Customer has 10 Records You Would Get a Single Value as Average.

Hope It Helps

Thnaks

Deepak

• ###### 2. Re: Average vs. Average per Customer

Thank you Deepak!!

I also am trying to figure out how to re-create the same answer that Tableau is giving me in Excel.

For example:  Using a subset of the Super Store data set:

Average Sales = 950

Average Sales per Customer = 3,312

I can re-create the average sales in Excel which is equal to 950, but re-creating Average Sales per Customer to be 3,312 in Excel is a different story.

I did a data extract in Tableau after getting the 3,312 average per customer, but it looks like an aggregate total for each customer's sales... what is Tableau dividing by to get 3,312 though...

Please let me know if it helps to attach the Excel worksheet I'm using for the Super Store data subset.

Thanks,

B

• ###### 3. Re: Average vs. Average per Customer

This value should be Different for each Customer.

• ###### 4. Re: Average vs. Average per Customer

Yes, they are all different for each customer.

• ###### 5. Re: Average vs. Average per Customer

what is 3312?

• ###### 6. Re: Average vs. Average per Customer

But what I'm wondering is how to get the answer in Excel.

Thanks,

B

• ###### 7. Re: Average vs. Average per Customer

3312 is the average sales per customer (using the reference line in Tableau), also found by using the INCLUDE formula up above:  I'll attach the picture of the workbook:

• ###### 8. Re: Average vs. Average per Customer

Oh, You are aggregating the aggregate , It is Average Of Average..What You have done is...

You Summed up the Avg per customer and Divided by Count Distinct of Customers. Do that in Excel

1 of 1 people found this helpful
• ###### 9. Re: Average vs. Average per Customer

Thank you so much!!!

• ###### 10. Re: Average vs. Average per Customer

• ###### 11. Re: Average vs. Average per Customer

Now, I get what I was missing (dividing sum by distinct # of customers):

If I wanted to find the average sales per transaction, I would use Average in the pill.

If I wanted to find the average sales per customer, I would use AVG({INCLUDE [Customer Name] : SUM([Sales])}).

Thank you!