Forum Discussion
Solved
Running Average of Group Data
Hello, I am trying to create a measure for calculating the running average of accumulated monthly sales by manager. This might be a bit confusing, so to further explain: I would like to sum all sales...
I assume this will work.
Avg Monthly Sales =
divide(
sum(data[sales]),
distinctcount(data[name])
)This assumes you put the manager's name and months in your visualisation
Thanks for your help, the "average" I am talking about would be the monthly moving average of all sales that took place under a specific sales manager. The sales manager might have 6 salesperson's under them one month and 5 the next. The monthly sales per salesperson is a total. So if there is a sales manager named Scott, I would like to keep a moving average for all Scott's sales peoples total monthly sales. Here is a little better example:
Manager name date sales ------------------------------------------------------ scott tim 01/01/2016 7800 scott pete 01/01/2016 5213 scott sally 01/01/2016 7812 scott tim 01/12/2015 2258 scott tom 01/12/2015 5412 scott pete 01/12/2015 9823 scott sally 01/12/2015 5214 mary david 01/01/2016 4382 mary hellen 01/01/2016 1465 mary dawn 01/12/2015 569 mary rick 01/12/2015 7821 mary steve 01/12/2015 9871 January 2016 Scott = (7800+5213+7812)/3 = 6941 December 2015 Scott = (2258+5412+9823+5214)/4 = 5676 Moving Average for Scott = (6941+5676)/2 = 6308
I hope this explains it better.
MattAllington
Community Champion
Community ChampionI assume this will work.
Avg Monthly Sales =
divide(
sum(data[sales]),
distinctcount(data[name])
)
This assumes you put the manager's name and months in your visualisation