topic Re: Finding the Mean, Median, and Range of a calculated DAX measure in DAX Commands and Tips

Finding the Mean, Median, and Range of a calculated DAX measure

RonD — Thu, 28 Jul 2022 05:32:58 GMT

Please help. I am almost certain that mine is a simple problem with a simple solution but I have spent quite a number of days on it without a breakthrough. Below is a sample of a large spreadsheet of patient data that I'm using to measure and visualize hospital performance on some metrics in Power BI Desktop. One of the metrics is the percentage of patients who have a pressure ulcer (an injury to the skin caused by prolonged pressure on the skin when patients are in bed for long periods).

Patient	Hospital	Date	Pressure Ulcer Present
101100	H01	28/07/2022	No
101101	H01	28/07/2022	Yes
101102	H01	28/07/2022	Yes
101103	H01	28/07/2022	Yes
101104	H02	28/07/2022	No
101105	H02	28/07/2022	No
101106	H02	28/07/2022	Yes
101107	H02	28/07/2022	No
101108	H03	28/07/2022	Yes
101109	H03	28/07/2022	No
101110	H03	28/07/2022	No
101111	H03	28/07/2022	Yes
101112	H04	28/07/2022	No
101113	H04	28/07/2022	No
101114	H04	28/07/2022	No
101115	H04	28/07/2022	Yes
101116	H05	28/07/2022	Yes
101117	H05	28/07/2022	Yes
101118	H05	28/07/2022	No
101119	H05	28/07/2022	No
101120	H05	28/07/2022	No

So for this example, I am using a DAX measure to calculate the Pressure Ulcer metric for each Hospital as a percentage of patients with a pressure ulcer as follows:

Pressure Ulcer Metric =

VAR Num = CALCULATE(COUNTROWS('Patients'), NOT(ISBLANK('Patients'[Pressure Ulcer Present])) && 'Patients'[Pressure Ulcer Present] = "Yes")

VAR Den = CALCULATE(COUNTROWS('Patients'), NOT(ISBLANK('Patients'[Pressure Ulcer Present])))

RETURN

IF(ISBLANK(Num), 0, DIVIDE(Num, Den))

By the way, patients that have a blank entry for the pressure ulcer are excluded hence the 'ISBLANK' check. So the results the above DAX code would be:

Hospital	Pressure Ulcer Metric
H01	75%
H02	25%
H03	50%
H04	25%
H05	40%

This is all working fine but after calculating the measure for each Hospital, I would like to calculate the Average, Median, Minimum, and Maximum across all the individual Hospital measures (in order to give hospital managers some idea of where they stand against other hospitals): this is what I am struggling to do. So for this example, I want to know how to calculate and get:

Average Pressure Ulcer Metric	43%
Median Pressure Ulcer Metric	40%
Minimum Pressure Ulcer Metric	25%
Maximum Pressure Ulcer Metric	75%

I have struggled to get this to work and I haven't seen anything similar to this on the forum. So I would appreciate some help. Thanks.

Re: Finding the Mean, Median, and Range of a calculated DAX measure

amitchandak — Thu, 28 Jul 2022 03:27:50 GMT

RonD , The above should give correct Avg -Pressure Ulcer Metric

try

AverageX(Values('Patients'[Patients]) , [Pressure Ulcer Metric])

MAxx(Values('Patients'[Patients]) , [Pressure Ulcer Metric])

Minx(Values('Patients'[Patients]) , [Pressure Ulcer Metric])

MedianX(Values('Patients'[Patients]) , [Pressure Ulcer Metric])

Re: Finding the Mean, Median, and Range of a calculated DAX measure

RonD — Thu, 28 Jul 2022 05:42:38 GMT

Thank you amitchandak. I realise that I may not have properly explained what I am trying to achieve and so I have edited my post to make it clearer. Your suggestion just reproduces the individual Hospital Pressure Ulcer Metrics which I am able to calculate with the DAX code in my post and a slicer on the Hospital column. But after calculating the measure for each Hospital, I would like to calculate the Average, Median, Minimum, and Maximum across all the individual Hospital measures (in order to give hospital managers some idea of where they stand against other hospitals): this is what I am struggling to do.

Re: Finding the Mean, Median, and Range of a calculated DAX measure

Anonymous — Mon, 01 Aug 2022 08:56:41 GMT

Hi RonD ,

I think you can try measures as below.

Average Pressure Ulcer Metric = AVERAGEX(VALUES(Patients[Hospital]),[Pressure Ulcer Metric])

Median Pressure Ulcer Metric = MEDIANX ( VALUES ( Patients[Hospital] ), [Pressure Ulcer Metric] )

Maximum Pressure Ulcer Metric = MAXX ( VALUES ( Patients[Hospital] ), [Pressure Ulcer Metric] )

Minimum Pressure Ulcer Metric = MINX(VALUES(Patients[Hospital]),[Pressure Ulcer Metric])

Result is as below.

Best Regards,
Rico Zhou

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

Re: Finding the Mean, Median, and Range of a calculated DAX measure

RonD — Wed, 03 Aug 2022 04:51:50 GMT

That works! Thanks Anonymous