Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I'm looking to compute the monthly average count within a pivot table. Below is an example of the pivot. I'm able to create the Count section, but anytime I try to incorporate the Monthly Avg, it doesn't let me compute the average of a COUNT by the dimension (which is MONTH). I believe I should be using RangeAvg, but haven't been able to get it to work properly.
Dimension:Month
Expression: count(ID)
Count | % of Monthly Avg | ||||||
ID | Jan | Feb | Mar | Monthly Average | Jan | Feb | Mar |
1 | 720 | 626 | 633 | 660 | 109% | 95% | 96% |
2 | 598 | 604 | 460 | 554 | 108% | 109% | 83% |
3 | 384 | 452 | 330 | 389 | 99% | 116% | 85% |
4 | 401 | 466 | 302 | 390 | 103% | 120% | 78% |
5 | 138 | 134 | 1,086 | 453 | 30% | 30% | 240% |
Ultimate Goal: I would like to highlight the count cell that is > 150% of the monthly average. Eg: ID=5, the counts jumped to 240% of what the monthly average usually is.
Any help is much appreciated!
Thanks,
Andrew
Maybe something like this as pivot table expression for your monthly average:
=Avg(TOTAL<ID> Aggr( Count(ID), ID, Month))