Forum Discussion

vbiqvitovs's avatar
vbiqvitovs
Frequent Visitor
9 years ago
Solved

SUM of Distinct Rows

I'm new to PowerBI and I can't seem to wrap my head around this calculation. My end goal is to look at the average duration of SQL Job executions over the past 7 days and compare this value to the mo...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    9 years ago

    Should be something along the lines of:

     

    Measure = CALCULATE(AVERAGE([Run Duration]),FILTER(Table,[Run Date]>TODAY()-7))
Greg_Deckler's avatar
Greg_Deckler
Community Champion
9 years ago

You would put SQL Job and this Measure in a table. If you have the relationship built between them, then the context should be correct and you will get the right answer.

vbiqvitovs's avatar
vbiqvitovs
Frequent Visitor
9 years ago

 The issue with this is that the duration specified in the data table is the duration of each step, instead of the higher-level job. This would only be a working solution if I wanted to get the average duration of the sub-steps, and not the job itself. I first need to sum all steps based on their association to the SQL job.

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    9 years ago

    Oh, can you post some data from your other table so that I can replicate this and get it working?

    • reddy's avatar
      reddy
      Frequent Visitor
      8 years ago

      Hi 

                                              ID           STATUS                           UPDATED_DATE     ASSIGNED

       

                                              1           INPROGRESS                    04-FEB-2018           1

                                              1           ONHOLD                          05-FEB-2018           1

                                              2           INPROGRESS                    06-FEB-2018           1

       

      when i taking sum(assigned) column it should be  2 for date range between 4feb to 5feb. but in chart the sum was 3. id 1 is hsowing for 1st week and it also showing for 2nd week. But Iam expecting it should take sum for only max date of each id. Ihave date range slicer also. I taking sunday as week end date.  Due weelky wise chart in each week it taking distinct sum. But i need sum for only max date of id. Here 1st feb is 4th week and 5th feb is second week in chart.

  • v-micsh-msft's avatar
    v-micsh-msft
    Microsoft Employee
    9 years ago

    Hi vbiqvitovs,

     

    how about creating a calculated column to show the Job duration for each day, then use this value in the formula which smoupre suggested.

    For the Job duration, which should be the format below:

     

    Job Duration = Sumx(
                   filter(
                      'DurationTable',
                      And('DurationTable'[SQL Job Name]=earlier('DurationTable'[SQL Job Name]),
                              'DurationTable'[Run Date]=earlier('DurationTable'[Run Date]))),
                   'DurationTable'[Run Duration])

    This should add up the Step time for a job by day.

    After that, try the formula below to get the average of the Job Duration:

    Durationaverage = calculate(Average([Job Duration]), 'DurationTable'[Run Date]>Today()-7)

    Well this formula might not be the right answer, regarding the average, how would you like to calculate it, for each job, or for all the jobs in the last 7 days?

    Please take a try with the formula above and see if that would meet your requirements.

    Reply back if you need any further assistance on this.

    Regards

    • vbiqvitovs's avatar
      vbiqvitovs
      Frequent Visitor
      9 years ago

      Thank you for your help, all. It looks like the issue I was running into had to do with how values are calculated using the KPI visualization. The goal was to look at duration over time and be able to identify possible variance. I thought the KPI visualization would be useful, but it looks like the Gauge visualization displays the values correctly. That being said, I still don't know why KPI is displaying values in an (seems to me) incorrect manner. Despite having the same values for corresponding data points (and identical filters), the KPI visualization seems to change the Target Goal value (Average Duration Week) arbitrarily.