Averaging groups of data

[spencer_ryan]

There may be a simple solution for this, but I can't seem to find it.

I have data in which I assigned a group value based on year and zip code.  Therefore each record is assigned to a group and there are roughly 50 groups.  I want to find the UNWeighted average price for each group... I could filter each group out based on tile number, but that would be 50+ filters I do not want to deal with.  I tried the Weighted average tool and it accomplishes the grouping side of what I want to do but does not do the intended calculation I am looking for.  Is there a simpler way to do this? I was looking into the multiple row too, but couldn't figure the proper way to accomplish this. Any help?

Thanks in Advance!

Spencer

[AdamR_AYX · Accepted solution]

Hi Spencer,

I think you just need to use the summarize tool for this.  "Group By" your group field and "Average" your price field.

Cheers
Adam

[LaurieLS]

HI Adam, I have the same issue but have run into problems using this Summarize  solution.

So I have 100K rows of Part numbers and many of them are duplicates that have different pc prices.

I need alteryx to merge the duplicate records so each part number has only one row, and then give me the avg price.

For Example:

In my bill of material the same part number can be listed 100 times with a different price...

I need them to somehow merge and then avg the price -

(So that each Part Number will only appear on one line in the output with the avg price calculated in another column)

I can group by part number and then avg the price in the Summarize tool but then 

I cannot get all the data from the original flow to pull through to the other side of the Summarize tool.

Do I need to group the Part Numbers  first somehow? because I cant seem to get the Formula tool to do this either.

So far all I can find is info on how to Avg fields/columns - not records/rows.

Any Suggestions are welcome. I have been spinning my wheels on this for too long.

Laurie

[RodL]

Laurie,

If I'm understanding your issue correctly, you just need to bring in a Join tool.

Connect your summarized data on one side and your detailed data on the other side and join on the basis of the part number.

This will add the average cost column to your detailed data.

Then not sure if this is a requirement, but if you only want to end up with one record for each part number, you can add a Unique tool after the Join, select the part number as the unique identifier, and the first record will come through the "U" side with everything else going to the "D" side. You can then use a Select to determine what resulting columns you want to remain (e.g., you would eliminate the original price column that you summarized since it is now meaningless).