cancel
Showing results for
Did you mean:
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

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors