SUM of Distinct Rows

[vbiqvitovs]

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 most recent execution, but the underlying structure of the data makes analysis pretty hard.

I'm not sure if I have to break down my data even further, or if this is just going to be one large DAX statement. So here goes...

I currently have a table with 8 columns. These columns are the SQL Job Name, Step ID, Run Duration, Run Date, Current Date, Past Week, Is Past Week, Is Today.

SQL Job Name has a one-to-many relationship with SQL Job Step (there are multiple steps in a job). 

 So I need to first calculate the sum of duration for all steps under a job on a given day, then average that value for the past 7 days. I don't know where to start on this in DAX, and I'm not sure (it may be obvious given the format of my table) if I should do this calculation as additional columns (data inflation?) or create a new table. I've gone through the DAX training provided on the Microsoft website, but couldn't find any functions that seemed to assist in this specific application.

[Greg_Deckler · Accepted solution]

Should be something along the lines of:

Measure = CALCULATE(AVERAGE([Run Duration]),FILTER(Table,[Run Date]>TODAY()-7))

[vbiqvitovs]

At first glance, this doesn't appear to address the  one-to-many relationship between the SQL Job and underlying steps. 

It's a good first step, I'll see if I can use this.

[Greg_Deckler]

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]

 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]

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]

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.

 

[Greg_Deckler]

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

[reddy]

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.