3 Replies Latest reply on Jan 12, 2018 1:25 AM by Jyothisree Rayagiri

# Computations with aggregate fields & graphing them

I have an excel sheet that contains each item that a person purchased at a grocery store, the day that it was purchased, and its price. I would like to compute and graph the average price per trip to the grocery store for each person against the number of trips they took.

Field names: Name, Date, Price

I compute Distinct Trips to the grocery store with COUNTD(DATE). I compute Total Spent at the grocery store across all trips with SUM(PRICE). I am able to make these two appear on a scatterplot with Distinct Trips as the X and Total Spent as the Y for each name, but I would like to see the average amount spent instead of the total spent. When I change SUM(PRICE) to AVG(PRICE) it gives me the average price per item purchased by that name. When I create my own custom calculation of Total Spent/Distinct Trips COUNTD(DATE) gets confused and divides by 1, so this calculation just returns Total Spent.

What am I doing wrong? How do I compute average amount spent per visit?

• ###### 1. Re: Computations with aggregate fields & graphing them

Hi

The Avg Calculation like sum([Sales])/countd([Order ID]) or sum([Sales])/countd([Order Date]) should give you correct values

I have attached a sample workbook using superstore data

/Jyothi

1 of 1 people found this helpful
• ###### 2. Re: Computations with aggregate fields & graphing them

Thanks, you were correct. I was trying to layer it within other not mentioned formatting which was causing the issue

• ###### 3. Re: Computations with aggregate fields & graphing them

Glad to have helped.