Overall average of group selected vs average of selected sub-set or individual within group

[Water]

Dear Experts,

I must calculate productivity of employees and compare their productivity as individuals to the productivity of the various groups they belong to. 

Productivity = Direct Hours/Total Hours.

There are many seperate entries per person per day  rolled up to months for each person in a big table with many other fields.

To ge the average % productivity per any selection I may make, I composed:

Average % M5 Productivity per selection =

AVERAGEX

(

SUMMARIZE

(

Productivity,

Productivity[Employee Name],

Productivity[Home Location],

Productivity[Fiscal Year],

Productivity[FISCYEARMONTH],

Productivity[FISCPD]),

[% M5 Productivity]

)

where 

% M5 Productivity = (SUM(Productivity[EMPDIRHRS])/SUM(Productivity[TOTALHRSTYPE]))

Question:

Each employee belongs to a Home Location. I want to compare the average productivity of an employee for any period selected against the average productivity of the Home Location the employee belongs to for that same period. 

Tried to use: 

Average of Home Location = CALCULATE([Average % M5 Productivity per selection], ALL(Productivity[Employee Name]))

but this only work sometimes as expected. I'm quite new to Power Bi and can't figure out why this inconsitant performance of the formula. So decided to ask for help, please!

Below a screenshot of a data subset by months for a selected year and Home Location.

The correct average of the total productivity of these three guys is 60.20% as per my DAX formala above (and not 60.11% as when using overall totals). 

Ben has an average productivity of 30% vs the 60.20% for the year selected for this Home Location, Darrel 82% vs the 60.2% etc. 

So what would be a good formula to get the Average productivity for the Home Location selected for any month and year selected which will stay constant when I select different Employee Names to be able to make the desired comparisons as described above?

One note: I cant use ALLCROSSFILTERED, as certain filters must be applied, e.g. EmployeeType is not Team Leader and EmployeeStatus is Active.

With hope, I thank you, dear experts!

Willem

[Water · Accepted solution]

Hi @AllisonKennedy,

I might have found a solution that seems to work. If I use your DAX and add:

Average 2 All Employees =

CALCULATE(

AVERAGEX(VALUES(Productivity[Employee Name]),[Average % M5 Productivity per selection]),

ALLEXCEPT(Productivity,Productivity[Home Location], Productivity[Month], Productivity[Fiscal Year]),

Productivity[EMPSTATUS] = "A", Not Productivity[EMPLOYEETYPE] in {"Team Leader","Foreman"}

)

or, with my DAX:

Average 5 of Home Location =

CALCULATE(

[Average % M5 Productivity per selection],

ALLEXCEPT(Productivity,Productivity[Home Location], Productivity[Month], Productivity[Fiscal Year]),

Productivity[EMPSTATUS] = "A", Not Productivity[EMPLOYEETYPE] in {"Team Leader","Foreman"}

)

it gives the expected outcomes. (Note some additional rules I had to add tweaking it for the correct output...)

Two things:

1. It works, but to be honest I don't really know why. I though ALLEXCEPT removes the filters, but the funny thing is Home Location, Month and Fiscal Year are actually the slicers applying filters. If I add Productivity[Employee Name] - the one which I thought the slicer should not be able to filter, it doesn't work anymore. So, it seems I don't really understand ALLEXCEPT.
2. I use drop-down list slicers. If I use the "SELECT ALL" option for Months, it suddenly applies the (correct) annual average next to each month, so a straight line in the graph. However, if I manually check all 12 months - thus also "all selected",  it works perfectly showing the individual different averages for each month. This I also don't understand. I deselected the "Show 'Select all" option"-option under Selection controls to prevent this from happening. 

The only work-around I could think of for my initial problem I was going to use if we can't find a Dax solution, was to create a separate graph for the group/Home Location average, and then disconnect the Employee Name slicer/filter from this graph to keep the group average not impacted by the Name selection.

Okay, above solutions I could find with my extremely limited Power BI knowledge. You will probably be able to compose a more elegant solution. Can't wait to see it!

Best regards, 

W

[Water]

Hi @AllisonKennedy 

Thank you for your response.

The article on averages is very insightful and I will revisit it until the last penny could drop. By luck though I think I am using the AverageX function correctly to ge the averages I am using so far.

The dimension I want to add, and this is my question, is how to add a seperate measure that will provide the overall average of the Home Location of a specific employee. So let say I filter on Ben, then I want a line graph to show his monthly productivity for each month of the year with one line (and my measure for this is working well), but as a second line in the same graph, want to show the avarage productivity of the group/Home location Ben belongs to as per the same period selection made. I am thus struggling with the Dax to show the average of the whole group for the same time period. My current measure for the group average is impacted by selections I make to zoom in on one or two employees at a time. How do I create a measure to calculate the group average based on similar selections of year and month and Home Loaction, but only that and not impacted by using the last drop-down filter to select an employee name. 

Probably an easy solution, but new to me and driving me nuts!

Thanks and best regards, 

W

[AllisonKennedy]

@Water  Your screenshots don't have the decimal values, so not sure on actual values and rounding. I'm also missing a level of detail on FISCPD which will impact my values, but if you can edit/provide sample data and describe the differences between attached and screenshot below and what you want, that would be helpful. 

Average of the Employees could be done taking AVERAGEX over the list of all employees but without seeing your raw data and all the filters you have applied I don't think I'm getting the big picture. Sorry, please help with more info.

[Water · Accepted solution]

Hi @AllisonKennedy,

I might have found a solution that seems to work. If I use your DAX and add:

Average 2 All Employees =

CALCULATE(

AVERAGEX(VALUES(Productivity[Employee Name]),[Average % M5 Productivity per selection]),

ALLEXCEPT(Productivity,Productivity[Home Location], Productivity[Month], Productivity[Fiscal Year]),

Productivity[EMPSTATUS] = "A", Not Productivity[EMPLOYEETYPE] in {"Team Leader","Foreman"}

)

or, with my DAX:

Average 5 of Home Location =

CALCULATE(

[Average % M5 Productivity per selection],

ALLEXCEPT(Productivity,Productivity[Home Location], Productivity[Month], Productivity[Fiscal Year]),

Productivity[EMPSTATUS] = "A", Not Productivity[EMPLOYEETYPE] in {"Team Leader","Foreman"}

)

it gives the expected outcomes. (Note some additional rules I had to add tweaking it for the correct output...)

Two things:

1. It works, but to be honest I don't really know why. I though ALLEXCEPT removes the filters, but the funny thing is Home Location, Month and Fiscal Year are actually the slicers applying filters. If I add Productivity[Employee Name] - the one which I thought the slicer should not be able to filter, it doesn't work anymore. So, it seems I don't really understand ALLEXCEPT.
2. I use drop-down list slicers. If I use the "SELECT ALL" option for Months, it suddenly applies the (correct) annual average next to each month, so a straight line in the graph. However, if I manually check all 12 months - thus also "all selected",  it works perfectly showing the individual different averages for each month. This I also don't understand. I deselected the "Show 'Select all" option"-option under Selection controls to prevent this from happening. 

The only work-around I could think of for my initial problem I was going to use if we can't find a Dax solution, was to create a separate graph for the group/Home Location average, and then disconnect the Employee Name slicer/filter from this graph to keep the group average not impacted by the Name selection.

Okay, above solutions I could find with my extremely limited Power BI knowledge. You will probably be able to compose a more elegant solution. Can't wait to see it!

Best regards, 

W

[AllisonKennedy · Accepted solution]

@Water  I created the DimEmployee table but then failed to use it in my first post. You should always use the Dim tables where possible in filters /slicers as well as in DAX to remove filters/slicers.

Does this give the result you want? 

Average of Home Location = CALCULATE([Average % M5 Productivity per selection], ALL(DimEmployee))

As for ALLEXCEPT, it's one that I continually need to view simple examples for. You may already be familiar with DAX.GUIDE but if not, check them out, they're super helpful: https://dax.guide/allexcept/ ALLEXCEPT returns a table of ALL rows of ALL columns in the original table EXCEPT the columns that are mentioned in the function. So, if you want to remove filter on EmployeeName, you do NOT want to put that column in the ALLEXCEPT function. 

Finally, if you want it to work for Home Location when you don't have a slicer for Home Location, then you need to add that filter back in to Home Location, such as using this DAX:

Average of Home Location =

VAR _HomeLocation = SELECTEDVALUE(Productivity[HOMELOCATION])

RETURN

CALCULATE([Average % M5 Productivity per selection], ALL(DimEmployee[EMPNAME]), KEEPFILTERS(DimEmployee[HOMELOCATION]=_HomeLocation))

As you can see in screenshot below, this gives different values for Chris and Ben, since they have different Home Locations: 

See attached updated file and send screenshots highlighting differences if still struggling.

[Water]

Hi @AllisonKennedy,

Thank you so much for all you help and time spent on this. You have been most kind!

I learned a lot through your feedback. 

Just stumbled over ALLEXCEPT – DAX Guide  yesterday while googling for a solution. Indeed an excellent source of information. 

Had to combine your solutions with an AllExcept and all seems to be working well now. 

Best wishes, 

W

[AllisonKennedy]

Thanks for the update @Water  and glad it's working. DAX is indeed a journey, so keep us posted on how you go! 

[Water]

Hi @AllisonKennedy

Thank you so much for all your help thus far.

Here a link to more granular data:

https://docs.google.com/spreadsheets/d/1rK73lPVCrg_8-KhITrowwPVtobnFWeuc/edit?usp=sharing&ouid=10237...

You will see each employee can have many time entries per month. 

With the below I tried to roll the data up to month level and then calculate the productivity per month per employee and have this as one row in the virtual table for the AverageX calculations.

Average % M5 Productivity per selection =

AVERAGEX

(

SUMMARIZE

(

Productivity,

Productivity[Employee Name],

Productivity[Home Location],

Productivity[Fiscal Year],

Productivity[FISCYEARMONTH],

Productivity[FISCPD]),

[% M5 Productivity]

)

where 

% M5 Productivity = (SUM(Productivity[EMPDIRHRS])/SUM(Productivity[TOTALHRS]))

Your dax formula, as well as my formula both seems to work well to provide the average productivity if all the employees at a Home Location are selected. However, the measures for the group/Home Location average are impacted by selections I make to zoom in on one or two employees at a time. So if I select Ben to see his average productivity through the slicer on Employee Names, it shows his average productivity, but this then impacts the output of the measure showing average productivity of the group/Home Location. 

How do I create a measure to calculate the group/Home Locaction average based on similar selections of year and month (and Home Loaction), but only that and not impacted by using the slicer to select an employee name? 

So let say I filter on Ben, then I want a line graph to show his monthly productivity for each month of the year (and my measure for this is working well), but as a second line in the same graph, want to show the avarage productivity of the group/Home Location Ben belongs to as per the same period selection made.

Want to graph the below two columns on the right in the same graph when I select either Ben, Darrel or John. Of course for all three the group/Home Location average will be the same for any period selected.

With thanks,

W

[AllisonKennedy]

@Water  Can you provide sample data in OneDrive link or even just paste table directly from Excel into this post if not too large?

[AllisonKennedy]

@Water 

What formula did you use to get the 60.2% desired result? 

Averages are always tricky. Here's my attempt at explaining what I think might be your issue: https://excelwithallison.blogspot.com/2020/09/what-does-average-mean.html 

If you're wanting the Average of the employee totals, you'll need to use AVERAGEX(DimEmployee, [Average % M5 Productivity per selection] ) 

Not sure if I've understood your problem correctly though.