How to do a KPI with multiple aggregates
So I have a data set that looks like this:
|A||1514||1||Jan 1 2017||541|
|A||46516||1||Jan 1 2017||2|
|B||151124||1||Jan 1 2017||2|
|B||24124||2||Jan 1 2017||2|
|B||124124||1||Jan 1 2017||53|
|C||123123||2||Jan 1 2017||5|
|D||15125121||1||Jan 1 2017||22|
|D||1125554||1||Jan 1 2017||1|
So I want to know how many groups have a unique count of ID that is over the average of the unique count .. if that makes sense.
So in this example:
A = 2
B = 3
C = 1
The average of these is 2 so the final value i'd be looking for in a KPI would be 1 since B is the only group that has a id count greater than the average.
Ideally I'd like to do this in a KPI that shows just this count and maybe the average value? The problem I've having is that it's doing multiple aggregations. I know I can adjust my table so that it does the count calculation but i'd be losing out on the granularity to be able to filter by other fields like type or date etc. Is it possible to do this KPI?
Thanks for any help that can be offered.