cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Impactful Individual

## Calculating the Median of a *subset* of a column

I have the above table I've constructed.  I'm pulling some measures into calculate columns, which is kinda weird, but please disregard that fact.

I've highlighted the 3 relevant columns.

[Median Regular] calculates the median of the [2018 Card Count %] column.  However, what I'd like to do, is calculate the median of the [2018 Card Count %] column, for each [2018_PmtCount_Bracket].

So say .69 is the median for the entire table.  But, what's the median for just those in the 100-10k bracket?  Those in the <100 bracet?  And so on.   I keep getting stuck on the MEDIAN function only accepting a column for its first argument:

Median per Bracket = median(calculate(Orgs[Median 2018 Card Count %],Orgs[2018_PmtCount_Bracket] = earlier(Orgs[2018_PmtCount_Bracket])))

^ Doesn't work.  I may also be using EARLIER incorrectly, it's been a long time since I've used it.

1 ACCEPTED SOLUTION
Impactful Individual

Gotcha, that's what I'd figured, though that still returned the median of the entire column, but...that was an excellent push in the right direction.  This equation works:

`Median per Bracket = CALCULATE(medianx(Orgs,[2018 Elec Count %]),filter(Orgs,Orgs[2018_PmtCount_Bracket] = earlier(Orgs[2018_PmtCount_Bracket])))`
4 REPLIES 4
Super User
`Median per Bracket = calculate( median(Orgs[Median 2018 Card Count %]]),`
` FILTER(Orgs, Orgs[2018_PmtCount_Bracket] = Orgs[2018_PmtCount_Bracket]))`

This is a measure. I copy and pasted your column names so give it a test.

*edited*

Impactful Individual

Thanks.  What does Table1 refer to in this?

Super User

Good question.  That's what happens from bad copy and pasting! I'll edit it now.

Obviously you can check it on your own system.

Impactful Individual

Gotcha, that's what I'd figured, though that still returned the median of the entire column, but...that was an excellent push in the right direction.  This equation works:

`Median per Bracket = CALCULATE(medianx(Orgs,[2018 Elec Count %]),filter(Orgs,Orgs[2018_PmtCount_Bracket] = earlier(Orgs[2018_PmtCount_Bracket])))`

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.