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!StudyPhase# of peopleDateDay numberStudy 1800 Cohort 1A220/10/20-1Study 1800 Cohort 1B121/10/20-1Study 1800 Cohort 1C125/10/20-1Study 1800 Cohort 1D126/10/20-1Study 1800 Cohort 1E202/11/20-1Study 1800 Cohort 1F218/11/20-1Study 1937 Cohort 1a 219/02/20-2Study 1937 Cohort 1b324/02/20-2Study 1937 Cohort 1c326/02/20-2Study 1937 Cohort 2a309/03/20-2Study 1937 Cohort 2b411/03/20-2Study 1937 Cohort 3a + FE414/06/20-2Study 1937 Cohort 3b + FE416/06/20-2Study 1937 Cohort 4a428/06/20-2Study 1937 Cohort 4b430/06/20-2Study 1937 Cohort 5a426/07/20-2Study 1937 Cohort 5b428/07/20-2Study 1937 Cohort 6a209/08/20-2Study 1937 Cohort 6b316/08/20-2Study 1937 Cohort 6c318/08/20-2

Forum Discussion

x-File

Helper I

3 years ago

Solved

Average recruitment rate

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

Need Help

Show and Tell

Anonymous

3 years ago

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

Average recruitment rate.pbix43 KB

3 Replies

Anonymous

Not applicable

3 years ago

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

Average recruitment rate.pbix42 KB

x-File
Helper I
3 years ago
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?

Anonymous

Not applicable

3 years ago

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

Average recruitment rate.pbix43 KB

Forum Discussion

Average recruitment rate

3 Replies

Related content

DAX Measure for Ranking the valid rows and calculating the average rating

Utilization Rate

Average Growth Rate

Average of a resolution rate

Calculate Average Rating