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.

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.