Forum Discussion

secure_101's avatar
secure_101
Frequent Visitor
9 years ago
Solved

Bradford factor Calculation

Hello All,    First time posting.   For those of you unaware f the Bradford factor, it is a formula used to calculate a figure based on absenteeism  S2 x D = B S is the total number of separat...
  • OwenAuger's avatar
    OwenAuger
    9 years ago

    Thanks for that Simon.

     

    Here is my suggested approach - uploaded with simplified data here:

    https://www.dropbox.com/s/gt1jytqobrc9ilt/Bradford%20Factor.pbix?dl=0

     

    1. Add a Block Index column to your original table, so that Block Index has a different value for each block of consecutive days with the same value of Abs, across all ClockNos.
      To do this, I carried out a series of steps in the Query Editor:
      1. Sort the rows by ClockNo & Date
      2. Group the rows by ClockNo & Abs, using GroupKind.Local, which groups each consecutive block of ClockNo & Abs values.
      3. Add a Block Index column to the resulting grouped table.
      4. Re-expand the grouped rows, then tidy up.
    2. Create a Separate Absences measure, which is a DISTINCTCOUNT of Block Index where Abs="2".
    3. Create a Days Absent measure, which counts rows of the table where Abs="2".
    4. Create a Bradford Factor measure equal to [Separate Absences] ^ 2 * [Days Absent]

     

    The measures in the end are:

    Separate Absences = 
CALCULATE (
    DISTINCTCOUNT ( 'Bradford Factor'[Block Index] ),
    'Bradford Factor'[Abs] = "2"
)

Days Absent = 
CALCULATE (
    COUNTROWS ( 'Bradford Factor' ),
    'Bradford Factor'[Abs] = "2"
)

Bradford Factor = 
IF (
    HASONEVALUE ( 'Bradford Factor'[ClockNo] ),
    // Only calculate for one ClockNo at a time, and don't aggregate ClockNos
    [Separate Absences]
        ^ 2
        * [Days Absent]
)

    Bradford Factor here doesn't aggregate ClockNos, but you could aggregate using AVERAGEX or some other method if you want.

     

    Hopefully this does what you expect and can be applied to your data model.

     

    Cheers,

    Owen

TomMartens's avatar
TomMartens
Super User
9 years ago

Hm,

 

I guess it is not that simple, as to use this simple formula:

B = 
CALCULATE(
  SUMX('Table', S2 * D)
)

Can you please provide some sample data

secure_101's avatar
secure_101
Frequent Visitor
9 years ago

Here's the data I'm working with....

 

 

 

  • TomMartens's avatar
    TomMartens
    Super User
    9 years ago

    Please excuse, but as I asked for some sampledata, I meant data, that I / we can easily reproduce, a downloadable textfile or pbix.

     

    I also realize, that I'm not able to identify the columns S2 and D you mentioned in your formula. Can you also please give some more information why it's important that day of absence are consecutive.

     

    Thanks 

    • secure_101's avatar
      secure_101
      Frequent Visitor
      9 years ago

      Sorry Tom

       

      I'm new to this program and this forum as well. 

       

      I thought by making it consecutive it would make it easier for me to visualise the total duration of time off and be able to see the multiple instances easier.

       

      All I want to do is count total absences and count instances of absences and do that calculation, 

       

      I'm not sure how to attach a file either....

       

      Simon

       

       

  • OwenAuger's avatar
    OwenAuger
    Super User
    9 years ago

    secure_101

     

    In the table you have shown:

    • Which column identifies the employee?
    • What values appear if an employee is present on a given day?
    • What values appear if an employee is absent on a given day?

    To make it easy to calculate the Bradford Factor with a measure, I am anticipating it might be best to add a column identifying the 'absence block'.

    • secure_101's avatar
      secure_101
      Frequent Visitor
      9 years ago

      Hello Owen

       

      The ClockNo identifies the employee, this is tied to a seperate database with a relationship that identifies the employees names.

       

      The Abs column contains the value that dictates whether they are absent or not, 2 is unauthorised, H is holiday ect.

       

      I am purely targeting the unauthorised at the minute.

       

      I appreciate any help you can offer

       

      Simon

      • OwenAuger's avatar
        OwenAuger
        Super User
        9 years ago

        Thanks for that Simon.

         

        Here is my suggested approach - uploaded with simplified data here:

        https://www.dropbox.com/s/gt1jytqobrc9ilt/Bradford%20Factor.pbix?dl=0

         

        1. Add a Block Index column to your original table, so that Block Index has a different value for each block of consecutive days with the same value of Abs, across all ClockNos.
          To do this, I carried out a series of steps in the Query Editor:
          1. Sort the rows by ClockNo & Date
          2. Group the rows by ClockNo & Abs, using GroupKind.Local, which groups each consecutive block of ClockNo & Abs values.
          3. Add a Block Index column to the resulting grouped table.
          4. Re-expand the grouped rows, then tidy up.
        2. Create a Separate Absences measure, which is a DISTINCTCOUNT of Block Index where Abs="2".
        3. Create a Days Absent measure, which counts rows of the table where Abs="2".
        4. Create a Bradford Factor measure equal to [Separate Absences] ^ 2 * [Days Absent]

         

        The measures in the end are:

        Separate Absences = 
CALCULATE (
    DISTINCTCOUNT ( 'Bradford Factor'[Block Index] ),
    'Bradford Factor'[Abs] = "2"
)

Days Absent = 
CALCULATE (
    COUNTROWS ( 'Bradford Factor' ),
    'Bradford Factor'[Abs] = "2"
)

Bradford Factor = 
IF (
    HASONEVALUE ( 'Bradford Factor'[ClockNo] ),
    // Only calculate for one ClockNo at a time, and don't aggregate ClockNos
    [Separate Absences]
        ^ 2
        * [Days Absent]
)

        Bradford Factor here doesn't aggregate ClockNos, but you could aggregate using AVERAGEX or some other method if you want.

         

        Hopefully this does what you expect and can be applied to your data model.

         

        Cheers,

        Owen