Average count of subgroups

[bigdataxhi]

Hi All,

I was hoping to get some help on the below which I cannot seem to find a solution for.

I need to create a chart which counts the average number of items per group/ sub-group, and also the deviation away from the average for each number in the sub-group.

Basically I am trying to calculate the mean/ average count of items per dimension group (the columns in yellow are where I need the help).

In the below example:

How to count number of sub-groups per Group

How to calculate average # of items in each Group.

Next step will be to calculate the deviation from the average for each Group

I assume I will need the aggr function, but I have little experience with Aggr and I am struggling to see how to apply the formula.

Many thanks!

[sunny_talwar · Accepted solution]

May be this

How to count number of sub-groups per Group

Count(DISTINCT TOTAL <Group> Sub-Group)

How to calculate average # of items in each Group.

Avg(TOTAL <Group> Aggr(Count(Items), Group, [Sub-Group]))

Stdev will be like this

Stdev(TOTAL <Group> Aggr(Count(Items), Group, [Sub-Group]))

[bigdataxhi]

Thanks - that was extremely helpful.