Avg(Aggr(Count(distinct ())) not working correctly

[JJaky]

Hi all,

I need to calculate the averages of distinct values per type per hour , and I'm using this formula: Avg(Aggr(Count(distinct Values), Type, Day, Hour)), but  this is not accurate.  See example pivot data for 1 day attached.

For example, we have 4885 unique IDs for this day, 57 types and 15 hours, which should give us 4885/57/15=5.7, but I'm getting 3.6. On the type level its ok, but the daily averages are not correct. 

Can someone advise how to correct this?

Thanks in advance!

[Or]

Your formula and your description seem to match, but your math doesn't match those near as I can tell. Your math is:

Count(distinct Values) / count(distinct Type) / Count(distinct Hour)

This is not the same thing as the average of the unique Value per type per hour, for example:

Value, Type, Hour

1, A, 1

2, A, 2

1, A, 2

In this scenario, there are two unique values, one type, and two hours, so your math is 2/1/2 = 1. However, the average unique values per hour is (1 + 2) / 2 = 1.5 since there is one distinct value in the first hour and two distinct values in the second hour.

[JJaky]

ok, but I have 4885 distinct values, 57 distinct types and 15 hours in a day. How should I calculate the average per type of product per hour?

[JJaky]

The question is: what is the average count of distinct values per type per hour? See the example attached above

[Or]

I don't know what the average count of distinct values per type per hour is for your data structure. Are you counting missing values as 0? If so, does that include missing combinations (e.g. if there are no values for the hour 1, would all of the types have a 0 associated with them for that hour)? How about hours for missing types (that have no Values associated with them for that day, but do exist in your data in general)? If the answer to all of those is yes, I'm guessing it'll be right around 3.6. [Edit] Allowing just for 24 hours in a day, I get 3.7, so I'm guessing there's 1-3 types that are suppressed from your Excel because they have 0 distinct values across the entire day, which would get us to 3.6 [/edit]