cancel
Showing results for
Did you mean:
Highlighted
Regular Visitor

## Variance From Average

I'm trying to figure out how replicate this table in Power BI.  I have all the first 5 columns in the report.  However, I'm not sure how i can grab the average unit/hour for all the employees while still keeping the report dynamic enough to modify itself with new filters (such as date or location) and then once I have that to perform the unit per hour - avg unit per hour for each indiviudal employee.

 Emp # Name Unit Hours Unit/Hour Variance from avg 1 Bob 10 15 0.666667 -1.365319865 2 Fred 11 18 0.611111 -0.611111111 3 Steve 9 11 0.818182 -0.818181818 0.698653
1 ACCEPTED SOLUTION

Accepted Solutions
Super User IV

## Re: Variance From Average

Here it is if Units/Hour is a measure:

```Measure 14 =
[mUnits/Hour]-AVERAGEX(SUMMARIZE(ALL(Table35),[Emp #],"__Units/Hour",[mUnits/Hour]),[__Units/Hour])```

You can actually use any aggregation that you find suitable. In the context of a single employee, all aggregations will return the exact same thing. The MAX of a single number is the number. The MIN of a single number is the number. The AVERAGE of a single number is the number. The SUM of a single number is the number. You get the idea. It's just there because it's a measure and you have to use some kind of aggregation for a column when referencing them from a measure.

But, the second part switches the context to ALL, so now we are not constrained by the context of the table, which is a single employee per row, we now have all employees. In the measure form above, we SUMMARIZE the entire table by Emp # and as our aggregation we specify the column name and then your Units/Hour measure. From that table, we AVERAGE all of the rows of the table for our __Units/Hour column in our SUMMARIZEd table.

If any of that still makes no sense what-so-ever, you want Page 15 of the attached PBIX file.

### I have book! Learn Power BI from Packt

Proud to be a Datanaut!

4 REPLIES 4
Super User IV

## Re: Variance From Average

Perhaps:

```Measure 13 =
MAX([Unit/Hour])-AVERAGEX(ALL(Table35),[Unit/Hour])```

### I have book! Learn Power BI from Packt

Proud to be a Datanaut!

Regular Visitor

## Re: Variance From Average

1) Do I have to turn units/hour into a calc column? Average is the average for a column.  Currently, units/hour is a measure because it isn't working correctly as a calc column.

here is the dax for the column

Divide(

SUM('Table'[Units]),

SUM('Table2',Hours]),

0)

2) I need average instead of max so why would Average([Units/hour]) - AVERAGEX('table'[Units/hour]) not equal 0?  I don't see why a filter and iterator would provide unique values in this situation.

Super User IV

## Re: Variance From Average

Here it is if Units/Hour is a measure:

```Measure 14 =
[mUnits/Hour]-AVERAGEX(SUMMARIZE(ALL(Table35),[Emp #],"__Units/Hour",[mUnits/Hour]),[__Units/Hour])```

You can actually use any aggregation that you find suitable. In the context of a single employee, all aggregations will return the exact same thing. The MAX of a single number is the number. The MIN of a single number is the number. The AVERAGE of a single number is the number. The SUM of a single number is the number. You get the idea. It's just there because it's a measure and you have to use some kind of aggregation for a column when referencing them from a measure.

But, the second part switches the context to ALL, so now we are not constrained by the context of the table, which is a single employee per row, we now have all employees. In the measure form above, we SUMMARIZE the entire table by Emp # and as our aggregation we specify the column name and then your Units/Hour measure. From that table, we AVERAGE all of the rows of the table for our __Units/Hour column in our SUMMARIZEd table.

If any of that still makes no sense what-so-ever, you want Page 15 of the attached PBIX file.

### I have book! Learn Power BI from Packt

Proud to be a Datanaut!

Regular Visitor

## Re: Variance From Average

I'm pretty sure this will do it.  I'll have to play around with this a little bit but it looks like it does what I need.  Thank you!

Announcements