Average recruitment rate

[x-File]

Hi all,

I work for a company that recruits people for different studies to participate in. What I am trying to see and visualize is how I can get an average recruitment rate number per study, preferably over different timelines.

For example (all data is in example table by the way), Study 1937-A recruited 2 people in Feb 19, 3 on Feb 24, and the last on August 18.

I would like to see the recruitment rate if that's possible, so just a simple card telling: average inclusion rate for study X is X people per week/month/quarter.

Is there a measure fot that? I tried but it didn't work.

Thanks in advance!

Study	Phase	# of people	Date	Day number
Study 1800	Cohort 1A	2	20/10/20	-1
Study 1800	Cohort 1B	1	21/10/20	-1
Study 1800	Cohort 1C	1	25/10/20	-1
Study 1800	Cohort 1D	1	26/10/20	-1
Study 1800	Cohort 1E	2	02/11/20	-1
Study 1800	Cohort 1F	2	18/11/20	-1
Study 1937	Cohort 1a	2	19/02/20	-2
Study 1937	Cohort 1b	3	24/02/20	-2
Study 1937	Cohort 1c	3	26/02/20	-2
Study 1937	Cohort 2a	3	09/03/20	-2
Study 1937	Cohort 2b	4	11/03/20	-2
Study 1937	Cohort 3a + FE	4	14/06/20	-2
Study 1937	Cohort 3b + FE	4	16/06/20	-2
Study 1937	Cohort 4a	4	28/06/20	-2
Study 1937	Cohort 4b	4	30/06/20	-2
Study 1937	Cohort 5a	4	26/07/20	-2
Study 1937	Cohort 5b	4	28/07/20	-2
Study 1937	Cohort 6a	2	09/08/20	-2
Study 1937	Cohort 6b	3	16/08/20	-2
Study 1937	Cohort 6c	3	18/08/20	-2

[v-yangliu-msft · Accepted solution]

Hi  @x-File ,

You can modify the function to the following form:

Flag2 =
VAR _select =
    SELECTEDVALUE ( 'Table'[Study] )
VAR _week =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[week] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Study] = MAX ( 'Table'[Study] ) )
    )
VAR _sum =
    SUMX (
        FILTER ( ALL ( 'Table' ), 'Table'[Study] = MAX ( 'Table'[Study] ) ),
        [# of people]
    )
VAR _averageweek =
    DIVIDE ( _sum, _week )
VAR _month =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[month] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Study] = MAX ( 'Table'[Study] ) )
    )
VAR _averagemonth =
    DIVIDE ( _sum, _month )
VAR _quarter =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[quarter] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Study] = MAX ( 'Table'[Study] ) )
    )
VAR _averagequarter =
    DIVIDE ( _sum, _quarter )
RETURN
    "average inclusion rate for study" & " " & _select & " " & "is" & " "
        & ROUNDUP ( _averageweek, 0 ) & " " & "people per week" & " / "
        & ROUNDUP ( _averagemonth, 0 ) & " " & "people per month" & " / "
        & ROUNDUP ( _averagequarter, 0 ) & " " & "people per quarter"

Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[v-yangliu-msft]

Hi  @x-File ,

Here are the steps you can follow：

1. Create calculated column.

week =
WEEKNUM('Table'[Date],1)

month = MONTH('Table'[Date])

quarter = QUARTER('Table'[Date])

2. Create measure.

Flag =
VAR _select =
    SELECTEDVALUE ( 'Table'[Study] )
VAR _week =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[week] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Study] = MAX ( 'Table'[Study] ) )
    )
VAR _sum =
    SUMX (
        FILTER ( ALL ( 'Table' ), 'Table'[Study] = MAX ( 'Table'[Study] ) ),
        [# of people]
    )
VAR _averageweek =
    DIVIDE ( _week, _sum )
VAR _month =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[month] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Study] = MAX ( 'Table'[Study] ) )
    )
VAR _averagemonth =
    DIVIDE ( _month, _sum )
VAR _quarter =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[quarter] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Study] = MAX ( 'Table'[Study] ) )
    )
VAR _averagequarter =
    DIVIDE ( _quarter, _sum )
RETURN
    "average inclusion rate for study" & " " & _select & " " & "is" & " "
        & FORMAT ( _averageweek, "Percent" ) & " " & "people per week" & " / "
        & FORMAT ( _averagemonth, "Percent" ) & " " & "people per month" & " / "
        & FORMAT ( _averagequarter, "Percent" ) & " " & "people per quarter"

3. Result:

If you need pbix, please click here.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[x-File]

Thank you very much! Just a quick question on this: why did you this in percentages? As in: is it possible to change this to (for example): per month 3 people are recruited on average instead of 22% is recruited?