Count of Account having Revenue above or equal to Average Revenue

[pramodkumbhare]

Hi All,

I am trying to get count of Account having Revenue above or equal to Average Revenue, but not able to do so. Please if someone can let me know if I am doing something wrong here.

Here is what i want, count >= Avg customer, in below case its 3. Below data is from Excel.

Account ID	Sales	Unique Account	Avg Revenue	>= avg customer	< avg customer
1	3082	1	2546.142857	1	0
2	784	1	2546.142857	0	1
1	100	0
3	222	1	2546.142857	0	1
4	5256	1	2546.142857	1	0
5	1380	1	2546.142857	0	1
6	86	1	2546.142857	0	1
7	6913	1	2546.142857	1	0
			Total	3	4

I am creating two measures as below:

1. Avg Sale by Accounts = CALCULATE(DIVIDE(sum(Table1[Sales]),DISTINCTCOUNT(Table1[Account ID])))

2. No. of Acc under AVG = CALCULATE( DISTINCTCOUNT(Table1[Account ID]),filter(Table1,[Sales] <= [Avg Sale by Accounts]))

But the result is not coming as execpted, it giving 7, which are total unique accounts. i want 3 there. See below pbix screenshot.

Please help 🙂

[mattbrice]

I would solve this problm with measures as I believe the calculated column solution won't provide the answer you want. I'll explain below.   I would make a few measures:

Total Sales = SUM ( Table1[Sales] )

Count of Accounts = DISTINCTCOUNT ( Table1[AccountID] )

Avg Per Account = [Total Sales] / [Count of Accounts]

Avg over all accounts = CALCULATE( [Avg Per Account], ALL(Table1[Account ID] ) )

No. Accounts under or equal Average =
CALCULATE ( COUNTROWS ( VALUES ( Table1[Account ID] ) ),
 FILTER (
 VALUES ( Table1[Account ID] ),
 [Total Sales] <= [Avg over all accounts]
 )
)


No. Accounts over Average =
CALCULATE (
 COUNTROWS ( VALUES ( Table1[Account ID] ) ),
 FILTER (
 VALUES ( Table1[Account ID] ),
 [Total Sales] > [Avg over all accounts]
 )
)

 Then put Table1[AccountID] on the rows, then select the measures you want.

Couple of points about the caclulated column solution.  First, the calculated column is comparing the row's sales value to average; not the total for the AccountID.  So this would lead to errors in marking rows above or below average.  Second, becuase an AccountID has multiple sales, the same AccountID will be counted either above or below average for every sale and not once per AccountID which is what i think the OP is wanting.  OP - let me know if I am interpresting things right.

And FYI, calculated columns are computed at data load time and therefore have no filter context, only a row context.   Row contexts are only transitioned to a filter context if you use CALCULATE, CALCULATETABLE, or one of the other table functions.  This is a long winded way of saying that instead of :

VAR Sales = CALCULATE(SUM([Sales]),ALL('Table') )

you can just do:

VAR Sales = SUM ( Table1[Sales] )

 and get the same result.

[Phil_Seamark · Accepted solution]

HI @pramodkumbhare,

You could just add these calcuated columns to your table and then add a SUM measure over the top

Above Average = 
VAR Sales = CALCULATE(SUM([Sales]),ALL('Table') )
VAR RowCount = CALCULATE(DISTINCTCOUNT('Table'[Account ID]),ALL('Table'))
Return IF([Sales]>=DIVIDE(Sales,RowCount),1,0)

Below Average = 
VAR Sales = CALCULATE(SUM([Sales]),ALL('Table') )
VAR RowCount = CALCULATE(DISTINCTCOUNT('Table'[Account ID]),ALL('Table'))
Return IF([Sales]<DIVIDE(Sales,RowCount),1,0)

[pramodkumbhare]

Thanks for this... It worked 🙂

Cheers.