Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
May be like this? PFA
May be like this? PFA
Hello!
=avg(aggr(
sum({$<[Category]=>} Count1)
/
sum({$<[Category]=>} total<[Calendar.Date Week End]> Count1)
,[Category]))
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()))
)