cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ruhor
Frequent Visitor

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 salesperson's sales for each month and assign that value to their manager, then calculate a running average from the earliest records in the database to date. Below a simplified example table:

 

 

Manager		name		date		sales
------------------------------------------------------
scott		tim		01/01/2016	7800
scott		pete		01/01/2016	5213
scott		sally		01/01/2016	7812
scott		ron		01/12/2015	2258
scott		tom		01/12/2015	5412
scott		chris		01/12/2015	9823
mary		mike		01/01/2016	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

On the dashboard there is a slicer for manager, and also a slicer for post date, so the user can analyze the dashboard data for a month or quarter, but i want this measure to average for all data in the database independent of all filters other than the manager we are looking at. I have "hacked out" several versions, none of which are working correctly, but here is my most recent version (which has been simplified for debug purposes) for a place to start. I know I need to get the data grouped by month

 

runningAverage =
CALCULATE (
    AVERAGE ( 'ruhor_views saleshistory'[sales] ),
    ALLEXCEPT ( 'ruhor_views saleshistory', 'ruhor_views saleshistory'[manager] )
)

Thanks.

1 ACCEPTED SOLUTION

 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



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

7 REPLIES 7
Anonymous
Not applicable

Hello folks.

I have a situation like @ruhor and I solve the problem with the Divide and distinctcount formula as @MattAllington said.

It's like the Average formula doesn't work on grouping columns. I'm using Matrix Visual to display the data, so each month is a column. In my case, the month is represented by the month's number.

What I can do if I want to calculate, for example, the previous 4 months average?

 

to create a rolling 4 month avearge, first add a unique month ID integer column that starts at 1 and increments by 1 for every distinct month in chronological order.  At the end of the first year the numbers should continue to 13, 14 etc.  Read about ID columns here http://exceleratorbi.com.au/power-pivot-calendar-tables/

 

Then you can write a formula like this.

 

Total Sales Rolling 4 Months:=
CALCULATE (
    [Total Sales],
    FILTER (
        ALL ( Calendar ),
        Calendar[MonthID] >= MAX ( Calendar[MonthID] ) -3
            && Calendar[MonthID] <= MAX ( Calendar[MonthID] )
    )
)


* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Anonymous
Not applicable

That's it! I forgot the FILTER formula before ALL(). Without it, the MAX doesn't work. I already have a calendar table for my objective, but anyway, many thanks for the prompt reply.

Best regards.

 

Hi,

 

I havent find my problem answer. I need to find a solutin that running average value based on the slicer selected value on the dash board.

 

I think there should be dynamic filter to catch up the slicer selected value and added the metric callculation value as a filter. Thus the average metric calculation work the correct data set and response correct average value.

 

Kind regards.

There are a few things here.

 

Firstly, even with simple test models, you should always build a proper data model.  This is needed for complex models, but it is also good practice and a good way to learn.  So you should set up a star schema data model (add a Calendar table and a Staff table).  You can read about data shape at my knowledge base here http://exceleratorbi.com.au/the-optimal-shape-for-power-pivot-data/

 

Now when you say Average, I assume you mean "average sales per sales person per month".  If you mean something else, then the answer will be different.

 

So what you are asking for here then is "All Time Sales to Date".  Place a filter on a date, and show me the average sales up until this point in time.  So using the star schema I describe above, the formula will be like this.

Average Sales =
      CALCULATE(AVERAGE(Sales[sales]),
             FILTER(all('Calendar'),
                    'Calendar'[date]<=MAX('Calendar'[date])
             )
      )

The formula (line 2) calculates the average sales, but only after the filter (line 3) kicks in.  The filter (line 3) removes the current filter context from the visualisation and the in line 4 it re-applys a new filter to keep all dates up until the current selected date in your visualisation.


I have a sample PBIX here https://dl.dropboxusercontent.com/u/30711565/running%20avg.pbix

 

 



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

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.

 

 

 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



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors