Forum Discussion

jfunderburk's avatar
jfunderburk
Frequent Visitor
8 years ago

Average is Not Calculating Correctly

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 emp...
Need Help
Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
8 years ago

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's avatar
jfunderburk
Frequent Visitor
8 years ago

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