5 Replies Latest reply: Sep 8, 2013 6:51 PM by Karl Pover RSS

    Same Sum, disregards dimensions in a chart, except one

      Hi all,

       

      I'm trying to get KPIs of my customer service, which i control every customers request as a CASE. I'm basically extracting data from Microsoft Dynamics CRM, which i have all my CASES, and analyzing them in QV. I need to know the average time my employees are executing services, then compare this average per service and per case.

       

      I already know the average of time of every service (picture 1), but when i'm trying to drilldown and compare this average per case and per employee, the average changes in the chart, like picture 2.

       

      I basically want to check if cases were solved according the average like picture 3. So, for all cases, the average must be the same as the value of picture 1.

       

      To calculate the average i'm using expression:

       

      sum(ceil((Aggr(sum([Case Spent Time]),[Service]))/

      (aggr(COUNT(DISTINCT{$<{[Case Status] = {'solved'}>}[Case Number]),[Service]))))

       

      I've already tried without the first sum, but instead 0 i got null. Also tried with aggr fuction, but didn't work as well.

      Captura de Tela 2013-08-31 às 21.43.09.png

      picture 1

      Captura de Tela 2013-08-31 às 21.54.20.png

      picture 2

       

      Captura de Tela 2013-08-31 às 21.51.32.png

      picture 3

       

      Will be very glad if someone could help me, it's driving me crazy.

       

      Best regards