Bradford factor Calculation

[secure_101]

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 separate absences by an individual
• D is the total number of days of absence of that individual
• B is the Bradford Factor score

I have a table which pulls from a time and attendance database, I have used filters and to get the data I require and have used Is Consecutive = COUNTROWS('TABLENAME) to get consecutive days of records. 

I'm now stumped, I need to calculate the above calculation, but all I get is syntax errors

[TomMartens]

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]

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

[OwenAuger]

@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]

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 · Accepted solution]

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

[secure_101]

Hello Owen.

I followed your instructions to the letter, but when I come to visualise the data it just shows a count of the number of absences.

I'm lost again.... have attached my saved file. 

https://www.dropbox.com/s/wd2ifxpu0qaxl1h/post%20bradford%20factor.pbix?dl=0

I'm gonna keep trying with it, but again I'd appreciate your help

Cheers

Simon

[OwenAuger]

No worries Simon 

The key part that was missing was, in the "Group By" step, you need to provide a fourth argument to the Table.Group function equal to GroupKind.Local. This ensures that contiguous blocks are treated as groups. This isn't an option in the "Group By' dialog box so you have to edit the code after creating that step. If you don't provide this argument, for example all the "2"s from one ClockNo are grouped together, regardless of whether they occurred in a contiguous block.

I would also suggest when you expand the grouped table, don't expand Abs and ClockNo (otherwise you end up with duplicate columns) and remove all the prefixes in the expanded column names.

Otherwise the measure definitions look fine 🙂

I've made those changes and reuploaded here

https://www.dropbox.com/s/t2po0u5qhgnl8sx/post%20bradford%20factor%20-%20Owen%20edit.pbix?dl=0

But I can't verify it works since I don't have access to your ODBC data source so the table can't update.

Please post back if it's not working or doesn't make sense.

Cheers,

Owen 🙂

[secure_101]

Thanks Owen

Thats worked perfect

You're an absolute Star, couldn't have done it without you.

Thanks again

Simon

[OwenAuger]

You're welcome Simon, glad to have helped 🙂

It was quite an interesting problem to look at - a little out of the ordinary which piqued my interest!

Cheers,

Owen

[TomMartens]

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]

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

[secure_101]

Hello All, 

First time posting.

For those of you unaware 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 separate absences by an individual
• D is the total number of days of absence of that individual
• B is the Bradford Factor score

I have a table which pulls from a time and attendance database, I have used filters and to get the data I require and have used Is Consecutive = COUNTROWS('TABLENAME) to get consecutive days of records. 

I'm now stumped, I need to calculate the above calculation, but all I get is syntax errors.

Cheers in advance

Simon