What is the best way to calculate the number of Ave Products that Customers Ordered?

[#1]

I have calculations for a Product Count as well as Customer Count based on selected criteria. This is helpful when I am looking at Products within a Product Group. Now I'm looking at a more macro level and need to know on Average how many Products that a Customer Ordered

I was seeing the combination use of Ave and Aggr functions, but this applied more to Sum metrics versus Counts.

Ideas?

[#2]

I wasn't sure if you meant the average quantity per order or the average number of distinct products per order. The good news is you should be able to use the AGGR function for either:

=AVG(AGGR(SUM([Qty]),[Order#])

or

= AVG(AGGR(COUNT(DISTINCT [Product ID]),[Order#])

[#3]

What would be best if this was brought up a level to consider Ave Products bought per Customer (eliminating the Order)?

Would it be: AVE(AGGR(COUNT(DISTINCT [Product ID],[Customer]? This would be a top level summary formula, right?

[#4 · Accepted solution]

Correct, you are on the right track replacing [Order#] with [Customer] to change what dimension is being used for the average. Closing the expressions we would end up with a final expression of:

=AVG(AGGR(COUNT(DISTINCT [Product ID]),[Customer])