Solved: Bradford factor Calculation - Page 2

secure_101 · ‎07-12-2017

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

OwenAuger · ‎07-12-2017

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

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.
Create a Separate Absences measure, which is a DISTINCTCOUNT of Block Index where Abs="2".
Create a Days Absent measure, which counts rows of the table where Abs="2".
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

Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post