Forum Discussion

Dayna's avatar
Dayna
Helper V
8 years ago
Solved

Aggregating over data sets

Hello,

 

I'm very new to PowerBI (come from a QlikView background) and attempting to calculate the 'absence' of data. We have a set of records for someones timesheet, and I want to work out the amount of 'day job' for that person, for that day. Day job is calculated by the sum of time for that person, for that day, and subtracted from 7.5. 

 

My data set looks like this:

 

DatePersonTypeHours
10/07/2018Joe BloggsSupport4
10/07/2018Joe BloggsProject2
11/07/2018Joe BloggsSupport4
11/07/2018Joe BloggsProject2
11/07/2018Bob SmithSupport2
11/07/2018Joe BloggsProject2
10/07/2018Bob SmithSupport1
10/07/2018Joe BloggsProject5

 

So for example, on 10/07/2018, Joe Bloggs did 4 hours support, 2 hours project, therefore 7.5 - 6 = 1.5 hour day job. 

 

I have no idea where to start with PowerBI with this, and would really appreciate some assistance!

 

Many thanks in advance,

 

Kind Regards,

Dayna

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    8 years ago

    Just create a measure along the lines of:

     

    Measure = 7.5 - SUM('Table'[Hours])

    Then just put that into a table visualization along with the other columns that you want to summarize by.

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    8 years ago

    Just create a measure along the lines of:

     

    Measure = 7.5 - SUM('Table'[Hours])

    Then just put that into a table visualization along with the other columns that you want to summarize by.

    • Dayna's avatar
      Dayna
      Helper V
      8 years ago

      That was easy! Thanks for the help!