Comparing individuals against total average

[menegocci]

I have a number of data entries for the employees in my department, and I'm trying to set up a pivot chart that would give me their average error rate in comparison to the average error rate of the whole department. I was able to create a Pivot Chart that shows me a line with the average for each one of the individual employees, and for the specific Teams as well, but I cannot figure out how to have one line where the total moving average is shown, so that I can compare how the individual employees/teams are doing in comparison to that base line. Any suggestions? I would like to achieve this with a pivot table, just because of the volume of data that I have to analyze daily, and the flexibility that it provides.

The raw data I have looks something like this:

[TABLE="class: cms_table"]
<TBODY>[TR]
[TD]Date[/TD]
[TD]Updater[/TD]
[TD]Errors[/TD]
[TD]Team[/TD]
[/TR]
[TR]
[TD]01/01/2013[/TD]
[TD]A[/TD]
[TD]6[/TD]
[TD]Team 1[/TD]
[/TR]
[TR]
[TD]01/01/2013[/TD]
[TD]B[/TD]
[TD]5[/TD]
[TD]Team 1[/TD]
[/TR]
[TR]
[TD]01/02/2013[/TD]
[TD]B[/TD]
[TD]3[/TD]
[TD]Team 2[/TD]
[/TR]
[TR]
[TD]01/03/2013[/TD]
[TD]C[/TD]
[TD]4[/TD]
[TD]Team 2[/TD]
[/TR]
[TR]
[TD]01/03/2013[/TD]
[TD]D[/TD]
[TD]5[/TD]
[TD]Team 1[/TD]
[/TR]
</TBODY>[/TABLE]



Thanks,​

 

[Jacob Barnett]

You need a DAX measure that opens out the filter context and only calculates based on dates earlier or equal to that selected. To get the weighting right you need to perform the calculation manually and then iterate it over the dates (presuming you want the average weighted by days).

The core of the main calculation measure is this which SUMS the errors on the dates equal to or earlier than the filter context:

=CALCULATE(SUM(Table1[Errors]),              
        FILTER(
             ALL(Table1),Table1[Date]<=VALUES(Table1[Date])
                  )
                 )

This is then combined with a COUNTROWS of the same thing to give the denominator and wrapped in some logic to ensure there is only a single date being used:

[Base Average]=IF(HASONEVALUE(Table1[Date]),           
                                 CALCULATE(SUM(Table1[Errors]),
                                    FILTER(
                                    ALL(Table1),Table1[Date]<=VALUES(Table1[Date])))/
                                 CALCULATE(COUNTROWS(Table1),
                                    FILTER(
                                    ALL(Table1),Table1[Date]<=VALUES(Table1[Date])))
                                     ,blank()
                                    )

This measure is then wrapped in a AVERAGEX() for the weighting:

=AVERAGEX(VALUES(Table1[Date]), [Base Average])

2 minute demo workbook here: https://docs.google.com/file/d/0Bz5yMU2oooW2RzBnUWQ2OXRkeXc/edit?usp=sharing

 

[menegocci]

Thank you for replying, Jacob! You definitely have put me on the right track to get to what I need. The code for the base average seems to return a very smooth trendline (I'm using line charts), with hardly any variation for the data, and I can't quite figure out why that is. If I simply select all of the updaters with just the AVERAGE function, I get exactly what I need for the aggregate, but I haven't found a way to keep that one line constant, while adding another one that will be responsive to the change in the slicers. Does that make sense?

Thank you again!

 

[menegocci]

I guess my follow up question the would be: Is there a way for the Base average function to mimic the Average of the total, with the only parameter being that it only goes until the last date that the filter is set up to?

 

[menegocci]

I will try to explain what I'm trying to achieve with some screen shots.
This is my Raw Data as of right now:

https://docs.google.com/file/d/0BwVnm04Fz9MNUU9xT0xWeHhWY28/edit?usp=sharing

The only difference from the sort of data I had on my first post is that I have a column called "Days from Start," which calculates how many days the employees have been working, so that I can compare people that started at different periods on the same basis (how many days they have been on the job). My average needs to be based on that, which I was sorta able to achieve using your formula.
My PowerPivot table looks like this:

https://docs.google.com/file/d/0BwVnm04Fz9MNM0Z3YjB1UTBucFk/edit?usp=sharing

And here is what I want to get at:

https://docs.google.com/file/d/0BwVnm04Fz9MNemo1UC15WUJKVlE/edit?usp=sharing

The Blue line ("Average Errors") is the result of the formula you gave me. I would like to be able to make it so that it is like the Moving average I get when I select all of the "Updaters," and create a trend line based on the data for 10 periods, because it offers me more granularity. To sum it up, I would like for the Moving Average to be the Static line, and then I would be able to compare any single updater, or a group of them against that by using the slicers. I feel like I'm really close to achieving it, but not quite there yet.
Thank you for the help!

 

[menegocci]

I figured out where my problem is! When I changed the formula to calculate the total average based on "Days from Start," the denominator of my average function is equal to the sum of every row up until the specific data point.

I.E.:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Days from Start[/TD]
[TD]TotalErrors
[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]

If my filter goes until "2" days from start, the formula returns a total average equal to the sum of errors up until the last day "2" (5+3+4+2+1=15), divided by the number of rows up until that point (5), which is equal to 5. I need the denominator of my average to be the count of unique values in the "Days from Start" column that come before the selected filter, so that when I select "2" it would take the Sum of Total Errors (Still 15) and divide it by how many "Days from Start" it's been (2), which would equal 7.5.

Which part of this formula do I need to change, and what would it look like?

Base Average:=IF(HASONEVALUE(Table1[Days from Start]),
           CALCULATE(SUM(Table1[TotalErrors]),
                 FILTER(
                      ALL(Table1),Table1[Days from Start]<=VALUES(Table1[Days from Start])))/
           CALCULATE(COUNTROWS(Table1),
                 FILTER(
                      ALL(Table1),Table1[Days from Start]<=VALUES(Table1[Days from Start])))
       ,blank()
       )

Thank you for the input!