Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mmace1
Impactful Individual
Impactful Individual

Calculating the Median of a *subset* of a column

Capture.JPG




 

 

 

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
mmace1
Impactful Individual
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])))

View solution in original post

4 REPLIES 4
HotChilli
Super User
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*

mmace1
Impactful Individual
Impactful Individual

Thanks.  What does Table1 refer to in this? 

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.

mmace1
Impactful Individual
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])))

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.