# Calculate median on a grouped column

Hi. I have a calculation that I need to solve and I’m not quite sure how to proceed. Here is a sample of my data.
Data Sample.xlsx (8.8 KB)

I need to group the ‘Identifier’ where ‘06 Mos Post-ALC ERs’ = 1

I used the following DAX to generate the start of code necessary to calculate the median of a grouped column.

EVALUATE

GROUPBY(
FILTER ( ‘ALC ER Visits’, ‘ALC ER Visits’[06 Mos Post-ALC ERs] = 1 ),
‘ALC ER Visits’[Visit HCN],
“Visit Count”,
COUNTX(
CURRENTGROUP(),
‘ALC ER Visits’[06 Mos Post-ALC ERs]
)
)

Here is the resulting table.

Grouped table.xlsx (6.6 MB)

I need to get a median of the Count of Identifier in the Grouped table. I’m not sure how to proceed. Can anyone offer some suggestions?

Review this post from @sam.mckay Calculating Median Value Using DAX In Power BI

1 Like

The article you reference uses Summarize. I’m not sure if it will accomplish what I am after. Will summarize group the data in the same way?

Power Query will make quick work of all this - basically one step:

You can also use the GroupBy command to calculate medians:

(I just added a random value column to your data for illustration purposes.

• Brian
1 Like

Thanks for the help!

Once I figured out how SUMMARIZE worked this worked out great. I ended up with the following formula.

06 mos Post ALC Med =
MEDIANX (
SUMMARIZE (
FILTER ( ‘ALC ER Visits’, ‘ALC ER Visits’[06 Mos Post-ALC ERs] = 1 ),
‘ALC ER Visits’[Visit HCN],
“Visit Count”, SUM ( ‘ALC ER Visits’[06 Mos Post-ALC ERs] )
),
[Visit Count]
)

That would be true if I only grouped on identifier, but grouping on both columns provides the same answer as if I filtered first:

• Brian
1 Like

This is awesome! Where possible I prefer to use Power Query in lieu of writing DAX. Unfortunately this particular solution will not work for me this time. The 06 Mos Post-ALC ERs values are calculated based on the difference between two dates. If the difference between the dates is 6 months or less, then the value is 1 otherwise it is zero. I was unable to find a way to calculate this in Power Query.

Any ideas on how I could do this in power query?

Piece of cake in PQ. If you can post some representative sample data, I’ll run through exactly how to do it.

• Brian

Cool @BrianJ
So here is some data. for you
Sample data 2.xlsx (12.7 KB)

The Visit HCN is the identifier.
The discharge date is a hospital discharge date.
The ER Admission Date/Time is an emergency room visit.

What I need to figure out in Power Query is:

Do you think this can be done? I couldn’t find any resources for this.

A little custom M and we’re off to the races…

1. Create a column stepping back six months from Admit Date (very similar to DAX DATEADD).

1. Test whether ER admit date is between Admit Date and column created in 1) above (1 if true, 0 if false):

And boom!..

From there, you can use this second column as the basis for your GroupBy transformations discussed above.

I hope this is helpful. Full solution file posted below.

WOW @BrianJ you da man! Thanks for your help. This had me stumped for days.

Glad to help. No reason to bang your head against the wall for days - when you get stumped just give the forum a shout. Lots of super knowledgeable folks here eager to assist.

• Brian