Forum Discussion

dalmn21's avatar
dalmn21
Frequent Visitor
1 year ago
Solved

PowerBI Avg. Ct.

I have a dataset of patients who visits a hospital that shows their ID and the time and day they arrive and leave. I was tasked to count the number of patients in the hospital at every 15 minute time interval. I was able to do that by creating a measure to count the unique patient IDs. Now they are wanting me to get the average count of patients in the hospital at every 15 minute time interval in a clustered column chart. That is where I'm stuck. I created a measure Avg. Pt. = AVERAGEX(VALUES('15 Min Ct'[StartDate], DISTINCTCOUNT['15 Min Ct'[PtID])), but I'm getting the sum rather than the average of however many days I select when I put it into the chart. 

If it helps any, the slicers/filters I am using for the chart are Facility, Department, Day of Week, Start Date, End Date. Any help would be greatly appreciated! 

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi dalmn21 ,
    Since I can't be sure what your data looks like, I modeled some example data

    Modified your original measure.

    Avg. Pt. = 
AVERAGEX(
    VALUES('15 Min Ct'[StartDate]),
    CALCULATE(
        DISTINCTCOUNT('15 Min Ct'[PtID])
    )
)

    Use calculate to ensure that calculations are done in context

    Using startdate as the x-axis

    Final output

     

    Best regards,
    Albert He

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

     

     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi dalmn21 ,
    Since I can't be sure what your data looks like, I modeled some example data

    Modified your original measure.

    Avg. Pt. = 
AVERAGEX(
    VALUES('15 Min Ct'[StartDate]),
    CALCULATE(
        DISTINCTCOUNT('15 Min Ct'[PtID])
    )
)

    Use calculate to ensure that calculations are done in context

    Using startdate as the x-axis

    Final output

     

    Best regards,
    Albert He

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