Reply
Highlighted
Regular Visitor
Posts: 27
Registered: ‎05-19-2017
Accepted Solution

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

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

 

TechnicianWeek EndingRepair Minutes
Technician 110-Mar129
Technician 210-Mar157
Technician 210-Mar165
Technician 210-Mar360
Technician 310-Mar316
Technician 310-Mar186
Technician 310-Mar333
Technician 310-Mar387
Technician 117-Mar67
Technician 117-Mar344
Technician 217-Mar137
Technician 217-Mar296
Technician 317-Mar392
Technician 317-Mar170

 

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?


Accepted Solutions
Super User
Posts: 10,775
Registered: ‎07-11-2015

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

[ Edited ]

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

 

 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


View solution in original post


All Replies
Super User
Posts: 10,775
Registered: ‎07-11-2015

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

[ Edited ]

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

 

 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!