Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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 an 8 times 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 an 8 times 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 an 8 times 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 an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.