Calculate median on a grouped column

DAX DAX Calculations
1

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?

2

Hi @dchapman374

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

1 Like
3

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?

4

@dchapman374,

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

Snag_13ffce69
Snag_13ffce691049×692 15.3 KB

Snag_1400018e
Snag_1400018e794×239 10 KB

You can also use the GroupBy command to calculate medians:

Snag_1400d123
Snag_1400d1231049×692 14.9 KB

image
image786×237 9.89 KB

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

I hope this is helpful.

  • Brian
1 Like
6

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]
)

7

@AntrikshSharma,

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

image
image1917×391 29.5 KB

  • Brian
1 Like
8

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?

9

@dchapman374,

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

  • Brian
10

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

The Visit HCN is the identifier.
The admit date is a hospital admission date.
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:
Is the ER Admission Date within 6 months prior to admission.

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

11

@dchapman374,

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).

image

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

image

And boom!..

image
image1161×424 38.4 KB

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.

12

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

13

@dchapman374,

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