Variance From Average

[dyee4613]

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

[Greg_Deckler]

Perhaps:

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

[dyee4613]

Thanks for your reply!  I have two questions

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.

[Greg_Deckler · Accepted solution]

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.

[dyee4613]

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!