3 Replies Latest reply on Feb 15, 2017 12:39 PM by Norbert Maijoor

# Using Non-Aggregated and Aggregated Fields to Determine Averages

I've got a data set which records every time a transaction happens with a product. Each product is classified by a model number and assigned a unique ID. Every time a transaction happens both of these are recorded.

I have to determine the average profit per product by model number. I've created a count of each product sold per model (Count of Products). When I try to divide Total Profit via Count of Products, I get the warning about aggregate versus non-aggregate. I used the ATTR function to fix this but values show zero for each field. I've uploaded a truncated workbook (10.1.4) to see what I'm working with and if there's a better way. Using the straight average function doesn't appear to work because each model number and product number are listed multiple times.

• ###### 1. Re: Using Non-Aggregated and Aggregated Fields to Determine Averages

Hi Melissa,

Find my approach as reference below and stored in attached workbook version 10.1

a.  1. Total Cost: [Acquisition Cost]+[Interest Cost]

b. 2. Total Revenue: [Sold Price]+[Rental Revenue]

c. 3. Total Profit: [2. Total Revenue]-[1. Total Cost]

d. Total Profit / Count of Products: sum([3. Total Profit])/count([Product IDNO])

1 of 1 people found this helpful
• ###### 2. Re: Using Non-Aggregated and Aggregated Fields to Determine Averages

Thanks, using the sum of profit worked in fixing the aggregate problem. I had to make a minor adjustment to account for the multiple rows. It works now.

• ###### 3. Re: Using Non-Aggregated and Aggregated Fields to Determine Averages

Hi Melissa,

Always NICE:) when "a plan comes together";) Thanks for the "Badge"