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))
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.

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.