Forum Discussion

sps-reporter's avatar
sps-reporter
Helper II
5 years ago
Solved

Wrong answer ... going crazy ... must be simple?

I've built a "fake" call center performance report and the answers are not coming out as expected. I am looking  find the total amount of time on the phone for the call center on 3 different shifts for each employee. I am doing this by computing the average time on the phone for each employee for each shift. Then computing the average talk time for each shift. Then I take the difference between the employee shift average and the shift average and multiply that by the number of each shift the employee work. This should tell me on average how much time employees spent on the phone compared to the average. Of course this is not a fair comparison because people worked different amounts of time. So, I normalize the average talk time difference by the ratio of the average employee work time to the employee's total work time. Thanks for making it this far.

 

So, I did this same calculation using Python Pandas which I am confident is giving me the right answers. The Power BI report is almost there and gives me the right answers for the measures but not the correct answer when I try to aggregate the normalized difference by employee. Plus, slicing by date everything is incorrect. Here is the PBIX File  I am at wits end on how to make this work. I'm fairly new to Power BI so I am probably missing something simple. Any advice would be greatly appreciated.
Thanks

Need Help

11 Replies

    • sps-reporter's avatar
      sps-reporter
      Helper II
      5 years ago

      Thanks Greg, your reply  is a solution to another issue that has been bugging me. This will come in handy. I've got more resolution on the problem I'm struggling with. Here are the measures in my PBIX:

       

      ///
      /// Want all of these to be calculated on data sliced by date
      ///


      // This should be the average Talk Time for each worker on each shift they worked
      Worker Shift Average =
      CALCULATE(
      AVERAGE(work_performance[Talk Time]),
      ALLEXCEPT(work_performance,work_performance[Date],work_performance[Name],work_performance[Shift])
      )

       

      // This is the count of each of the shifts they worked
      Worker Shift Count =
      CALCULATE(
      COUNTA(work_performance[Shift]),
      ALLEXCEPT(work_performance,work_performance[Date],work_performance[Shift],work_performance[Name])
      )

       

      // This is the average Talk Time of the shifts for all workers
      Shift Average =
      CALCULATE(
      AVERAGE(work_performance[Talk Time]),
      ALLEXCEPT(work_performance,work_performance[Date],work_performance[Shift])
      )

       

      // Total hours worked by each worker
      Total Hours Worked =
      CALCULATE(
      SUM(work_performance[Shift Length]),
      ALLEXCEPT(work_performance,work_performance[Date],work_performance[Name])
      )

       

      // Average of the total hours worked by each worker
      Average Hours Worked =
      CALCULATE(
      DIVIDE(SUM(work_performance[Shift Length]),DISTINCTCOUNT(work_performance[Name])),
      ALLEXCEPT(work_performance,work_performance[Date])
      )

       

      // Total Difference of worker shift average minus the overall shift average Talk time
      // Total because this average difference is multiplied by the number of each of the shifts
      // the worker worked
      Total Difference =
      CALCULATE(
      [Worker Shift Count]*([Worker Shift Average]-[Shift Average])
      )

       

      // The total difference is normalized such that workers that worked less than the average
      // time will have an increase total diffence and those that worked more than the average
      // time will get a reduction in their total difference
      Normalize Total Difference =
      CALCULATE(
      [Total Difference]*[Average Hours Worked]/[Total Hours Worked]
      )

      A table of these measures is not really of interest other than to spot check things. What I want is a plot (below) of the sorted normalized total difference over the workers to see how they compare, sort of a ranking. When I do this the normalized total difference measure ignores the shift-wise nature of this calculation and the total difference is simply the average Talk Time for each worker regardless of the shift and the average talk time of all the workers. This is a different calculation and not the one I'm interested in because each worker performs differently on each shift.

      I'm sure this is a simple solution I am just at a stand still. I updated the PBIX file

      Thanks