What's the best way to illustrate weighted contributors to an average?

[Anonymous]

Say I'm reporting on average repair minutes for mechanical technicians. Every week I average all cases across all technicians. Here's a dataset:

Technician	Week Ending	Repair Minutes
Technician 1	10-Mar	129
Technician 2	10-Mar	157
Technician 2	10-Mar	165
Technician 2	10-Mar	360
Technician 3	10-Mar	316
Technician 3	10-Mar	186
Technician 3	10-Mar	333
Technician 3	10-Mar	387
Technician 1	17-Mar	67
Technician 1	17-Mar	344
Technician 2	17-Mar	137
Technician 2	17-Mar	296
Technician 3	17-Mar	392
Technician 3	17-Mar	170

So for Week Ending 10-Mar, I average 254.1 minutes. For Week Ending 17-Mar, I average 234.3 minutes.

I want to represent for their managers the way each technician contributed to the week over week change in average. It would be great to do that in a waterfall-style chart, but since we're dealing in averages instead of totals, it doesn't work the same way. 

Is there a good way to go about this in Power BI?

[Greg_Deckler · Accepted solution]

So this was going great right up until the end, I kind of had to guess at what you were going for, but perhaps try creating a table like this:

Table = VAR myTable = SUMMARIZE(RepairMinutes,RepairMinutes[Technician],RepairMinutes[Week Ending],"Avg",AVERAGE(RepairMinutes[Repair Minutes]))
RETURN myTable

RepairMinutes is the name I gave to the table of data that you provided (that's the way to do it!).

Then you can create a column like this:

Column = 
VAR lastweek = MINX(FILTER('Table',
                       'Table'[Technician]=EARLIER('Table'[Technician])
               ),'Table'[Week Ending])
VAR lastweekAvg = MINX(FILTER('Table','Table'[Technician]=EARLIER('Table'[Technician]) && 'Table'[Week Ending]=lastweek),'Table'[Avg])
VAR PercentChange = (lastweekAvg - 'Table'[Avg])/lastweekAvg
RETURN PercentChange

 You should end up with a table like this:

Technician         Avg                           Week Ending                     Column

Technician 1	129	3/10/2018 12:00:00 AM	0.00
Technician 2	227.333333333333	3/10/2018 12:00:00 AM	0.00
Technician 3	305.5	3/10/2018 12:00:00 AM	0.00
Technician 1	205.5	3/17/2018 12:00:00 AM	-0.59
Technician 2	216.5	3/17/2018 12:00:00 AM	0.05
Technician 3	281	3/17/2018 12:00:00 AM	0.08