cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
dyee4613 Regular Visitor
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 #NameUnitHoursUnit/HourVariance from avg
1Bob10150.666667-1.365319865
2Fred11180.611111-0.611111111
3Steve9110.818182-0.818181818
    0.698653 
1 ACCEPTED SOLUTION

Accepted Solutions
Super User IV
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


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

Proud to be a Datanaut!

View solution in original post

4 REPLIES 4
Super User IV
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


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

Proud to be a Datanaut!

dyee4613 Regular Visitor
Regular Visitor

Re: Variance From Average

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.

Super User IV
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


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

Proud to be a Datanaut!

View solution in original post

dyee4613 Regular Visitor
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!

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors