Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to 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
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] ) ) )
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
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |