Average is Not Calculating Correctly

[jfunderburk]

Hi all,

I have a set of data where I am trying to take an average of orders completed by an employee divided by their hours worked; however, it is not averaging correctly. The order shows the employee's hours on each row of the data, so I believe it is summing each row, when it should be grouping by day. I cannot use a summarize function because it takes away the ability to export the underlying data online.

How could I do the average function with a group by the date?

Here is a link: https://www.dropbox.com/s/rxlu2cwqd2or3us/Test.pbix?dl=0

-Josh

[Phil_Seamark]

HI @jfunderburk

If I think I understand what you want, you are after an average over the daily average?

If so, this calculated measure gets close

Alternate Measure = AVERAGEX(ALL('TestData'[date]), DIVIDE(count(TestData[orderid]),sum(TestData[hours])))

[jfunderburk]

Hi Phil,

I don't think an average of a daily average is the correct way to put it. It should be the count of total orders (by order id) divided by the sum of the hours worked.

The way the query is set up is to join the hours worked per day to each column, so when it counts the columns all is fine, but when it sums the hours without grouping by date, the formula thinks the employee has worked the hours for the day times the amount of columns.

Check out the TestData on my example to see what I mean. 

I see two possible solutions: 1. Figure out how to do a "group by" date in the sum function 2. create a new table with employee hours that can have a relationship between both the date and the employee.

I uploaded an example with the TestDate and also one where I summarized the data to get the result I want. Remember that the summarized table does not allow me to export the order ids associated to the data, so I am avoiding use it. Link: https://www.dropbox.com/s/rxlu2cwqd2or3us/Test.pbix?dl=0

-Josh