Show count of branches where branch value > avg value?

[#1]

I am trying to figure out how to show a count, by month, of branches whose paid percent is greater/less than that month’s average paid percent (across all branches).

Please see the qvw attached for sample data.  If I add branch as a dimension, I can get an individual count.  But I can’t figure out how to either roll it up to the month level or (ideally) do it w/o having branch as a dimension since I don't really need it displayed. 

What I have:

What I want:

Any suggestions?

[swuehl · Accepted solution]

Hi,

use

=count(aggr(if((Sum(total<Branch,Month>Paid)/Sum(total <Branch,Month>Billed))>(Sum (total <Month> Paid) / Sum (total <Month> Billed)),count(distinct Branch)) ,Branch, Month))

as expression for Branches > Co Avg.

Stefan

[#3]

That's exactly what I needed - thanks!