How to count no of occurrences above average

[#1]

Hello All,

I ran into a scenario , seems to be very simple ask.

Let me start with a scenario , I have group and subgroup and values are for the subgroup. Sample data provided below

Group	Subgroup	Value
Group 1	SubGroup1	10
Group 1	SubGroup2	20
Group 1	SubGroup3	30
Group 1	SubGroup4	40
Group 1	SubGroup5	50
Group 2	SubGroup6	110
Group 2	SubGroup7	120
Group 2	SubGroup8	130
Group 2	SubGroup9	140
Group 2	SubGroup10	150
Group 2	SubGroup11	160
Group 2	SubGroup12	170

I would like to build a chart which shows, Group in the dimension, Average for this group "Sum(Value)/count(subgroup)" which is easy. Now the third expression which shows how many subgroups are greater than average.

Group	Average	Count of Subgroups above Average
Group1	30	2
Group2	140	3

Note: Please suggest how can I handle it in the expression directly not in the load script.

Thank you for going over this and giving it a try.

[sunny_talwar · Accepted solution]

You can try this expression for counting:

=Sum(If(Aggr(Value, Group, SubGroup) > Aggr(Avg(TOTAL <Group> Value), Group, SubGroup), 1, 0))

[#3]

Thank you! it worked.

Curious, why TOTAL function on the right side of the comparison operator?

[sunny_talwar]

Because my aggregate has two dimensions and I need the Avg by just Group.