I have a list of UPC codes and combined weekly sales totals separated by commas in the next column. The flip table gives each UPC it’s own column. I then use the average function to get the average for each group. It appears that the average is based on the total number of rows which returns an incorrect average for items that are new or out of stock for several weeks. Is there a way to set the denominator to equal the total number of not blank cells in the column? Or better yet, average all comma separated values in a single cell by the number of entries in that cell?
Hey @THOMAS_A_HUSMANN - Ah, yep, we need to ensure that your denominator is being set properly by the grouping step. If you don’t set a grouping column, it just uses the total rows, which would be inaccurate.
You can either backtrack so that the cells are not merged, and there is 1 row per UPC + Sales Total, or you can use a Split Column step but use the Split into New Rows option. This would also result in 1 row per UPC + Sales Total.
Once you do that, you can use the Average by Group option - Average the Sales Total column, and group the Averages by the UPC column.
Let me know how that goes!
1 Like