Help calculating an average

[#1]

Hi,

I have created the first 3 columns in the below straight table. This shows the '% Logged' by 'Category' and 'Week Ending'

The '% Logged' is calculated with the following expression.

=sum({$<[Category]=>} Count1)
/
count({$<[Category]=>} total<[Calendar.Date Week End]> [Call Start])

I now want to create a 4th column (as shown) which provides and average '% Logged' for each 'Category'

I've tried the below expression (and others!) but haven't been able to achieve the desired result... Can anyone help with the correct expression for this??

=aggr(
sum({$<[Category]=>} Count1)
/
sum({$<[Category]=>} total<[Calendar.Date Week End]> Count1)
,[Category])

Attached is a sample app which may help

Category	Week Ending	% Logged	Average
TestA	22/02/2015	2.06%	2.77%
TestA	01/03/2015	2.30%
TestA	08/03/2015	2.52%
TestA	15/03/2015	2.68%
TestA	22/03/2015	2.37%
TestA	29/03/2015	2.89%
TestA	05/04/2015	3.86%
TestA	12/04/2015	3.49%
TestB	22/02/2015	2.36%	3.12%
TestB	01/03/2015	3.35%
TestB	08/03/2015	3.10%
TestB	15/03/2015	3.18%
TestB	22/03/2015	2.87%
TestB	29/03/2015	3.34%
TestB	05/04/2015	3.02%
TestB	12/04/2015	3.76%
TestC	22/02/2015	2.41%	2.46%
TestC	01/03/2015	2.24%
TestC	08/03/2015	2.78%
TestC	15/03/2015	2.63%
TestC	22/03/2015	2.33%
TestC	29/03/2015	2.38%
TestC	05/04/2015	2.30%
TestC	12/04/2015	2.58%

Thanks

[tresesco · Accepted solution]

May be like this? PFA

[pokassov]

Hello!

=avg(aggr(
sum({$<[Category]=>} Count1)
/
sum({$<[Category]=>} total<[Calendar.Date Week End]> Count1)
,[Category]))

[#4]

Thanks a lot tresesco, you're reply got me on the right track. The expression I used in the end was

=bottom(
rangeavg(above(
sum({$<[Category 3/2]=>} Count1)
/
sum({$<[Category 3/2]=>} total<[Calendar.Date Week End]> [Count1])
,0,rowno()))
)