power BI report showing the average number of patients present per hour/ per day

[jelle87]

Dear Community,

I have a dataset containing 28.000 + rows of hospital data (patient arrival at a site and time of departure)
Each row represents:
- visit ID = ID of each patient visit
- locatie = location of the visit
- startDat = datetime of arrival @ locatie
- eindDat = datetime of departure @ locatie

I want to make a bar chart showing on average: how many patients are present during each hour of the day. E.g. on average, 6 patients are present between 9 am and 10 am at location A, 7 patients at location B etc...
And do the same for each day of the week

The result should be something like the following. (Here I almost reached the desired output  by reshaping the dataset to a 1 hour per patient per row format, but this gives me performance issues when the dataset becomes large + result is not 100% correct e.g. nulls are not included in averageing)

Anonymized pbix: link to pbix 

Thanks in advance
J

[Vince90]

Hello!

After searching for a while, this is the first model I can use based on the same initial question: showing how much patients who are present per hour.

Only one problem remains: how do you include patients who arrived the previous day?

Thanks in advance!

Vince

[jelle87]

Dear @amitchandak 

Thank you for the suggestion, I have created the 2 columns and tried to create the measure as well but this gives an error

this is the pbix with the 2 columns created, could you please try to produce the result?

https://www.dropbox.com/s/2a3f1l4h4qz0o7v/example_count_patients_present_per_hour.pbix?dl=0 

@v-deddai1-msft Thank you as well, your solution was very close to what I wanted, with the exception that in the hourly graph, the average is not calculated correctly (since averageX does not take into account the nulls, which occur often at night. e.g. at 22:00, 23:00 the average it too high)

[v-deddai1-msft]

Hi @jelle87 ,

Please try to use the following measure:

averageperhour1 =
VAR a =
    ADDCOLUMNS (
        CROSSJOIN ( VALUES ( Source[StartDatOnly] ), VALUES ( TimeTable[Hour] ) ),
        "numberofvisit",
            CALCULATE (
                COUNT ( Source[visitID.2] ),
                FILTER (
                    ALLEXCEPT ( Source, Source[LOCATIE] ),
                    Source[StartDatOnly] = EARLIER ( Source[StartDatOnly] )
                        && HOUR ( Source[startDat] ) <= EARLIER ( TimeTable[Hour] )
                        && HOUR ( Source[eindDat] ) >= EARLIER ( TimeTable[Hour] )
                )
            )
    )
RETURN
    AVERAGEX ( a, [numberofvisit] )


averageperday =
VAR a =
    ADDCOLUMNS (
        CROSSJOIN ( VALUES ( Source[StartDatOnly] ), VALUES ( 'Date'[weekdaynumber] ) ),
        "numberofvisit",
            CALCULATE (
                COUNT ( Source[visitID.2] ),
                FILTER (
                    ALLEXCEPT ( Source, Source[LOCATIE] ),
                    Source[weekday] = EARLIER ( 'Date'[weekdaynumber] )
                        && Source[StartDatOnly] = EARLIER ( Source[StartDatOnly] )
                )
            )
    )
RETURN
    AVERAGEX ( a, [numberofvisit] )

For more details,please refer to pbix file.

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

Best Regards,

Dedmon Dai

[jelle87]

Dear v-dedda1-msft

Thank you for your reply, the result is very close to what I need, there is only 1 problem, between 21:00 and 23:59 the graph shows an average of 1 patient for each catergory. But this is not correct. The real average is a lot lower because often the number of patients between 21:00 and 23:59 is null, and nulls are not included in the calculation of the average the way it is calcualted now. Using divide may be a better option? But I'm not sure how to do that

[amitchandak]

@jelle87 . make sure you have these two column

startDat date  =[startDat].date // datevalue([startDat])

hour = hour([startDat])

and measure like

AverageX(table, Table[startDat date], Table[hour], "_1", countrows(Table)), [_1])