Removing Outliers to Produce an accurate Median

[JellyFishBi]

Hello Datanuts!
I am analyzing helthcare providers for the Median patient Exams.  I can't seem to exclude my outliers:

The formula below gives an essence of what I am driving at.  I hope that one of you smart folks can spot the error in my ways.

MedianTotalExamCount =

CALCULATE(

MEDIANX(

SUMMARIZE(

BizLine,BizLine[ProvName],"TotExam",'4010'[TotalExam]),[TotExam]),

FILTER('4010',[TotalExam]>10))

[v-piga-msft]

Hi @JellyFishBi ,

More details will be much helpful.

It seems that your formula has no syntax error. 

If it is convenient, could you share some data sample and your desired output so that I could have a test on it?

Best  Regards,

Cherry

[JellyFishBi]

Hello @v-piga-msft Thank you for your response! As you an see in the snip, I have fields, TotalExam, RankTotalExam(For Reference) and MedainTotalExam. Unfiltered, the Median of TotalExam is 114 which looks correct as 114 falls in the middle of the data set.

Here is the code to achieve this:

TotalExam.
The syntax here is:

MedianTotalExam =

CALCULATE(

MEDIANX(

SUMMARIZE(

BizLine,BizLine[ProvName],"TotExam",'4010'[TotalExam]),[TotExam]),

FILTER('4010',[TotalExam]>30))

Unfiltered

What i would like to do is produce a median which excludes all TotalExam records < than 30 in

TotalExam.  My desired output is to get the median of the filtered record set.

The syntax here is:

MedianTotalExam =

CALCULATE(

MEDIANX(

SUMMARIZE(

BizLine,BizLine[ProvName],"TotExam",'4010'[TotalExam]),[TotExam]),

FILTER('4010',[TotalExam]>30))

The Results of this are this:

Filtered

Filtering >30 Returns a null column.  Any ideas would be helpful.
Thanks for your response.

Mike

[richbenmintz]

Hi @JellyFishBi ,

Using a very simplified version of your model, bizline 1->* to 4010 and the following measure i believe i get the desired results

MedianTotalExamCount = 
MEDIANX(
CALCULATETABLE(SUMMARIZE(

'BizLine','BizLine'[ProvName],"TotExam",[TotalExam]), filter('4010', [TotalExam]>30)),[TotExam])

now if you want the median to be the same for all bizline rows for calculate a distance for each bizline the formula is 

MedianTotalExamCount = 
CALCULATE(MEDIANX(
CALCULATETABLE(SUMMARIZE(
'BizLine','BizLine'[ProvName],"TotExam",[TotalExam]), filter('4010', [TotalExam]>30)),[TotExam]), ALL(BizLine[ProvName]))

Hope that Helps!

[JellyFishBi]

@richbenmintz 

Hi RIch, thanks for the reponse.  For both of your solutions, my data returns a blank column.  Also, what does  bizline 1->* to 4010 mean?

Many thanks for your response.

Mike

[richbenmintz]

Hi @JellyFishBi ,

Can you provide an anonymized version of your .PBIX would allow me to get the correct formula for you

[JellyFishBi]

Thanks!

Here's a link to my google drive.  Thanks!

Medainx Outliers Pbix

@richbenmintz 

Thanks

[richbenmintz · Accepted solution]

@JellyFishBi 

Try this one

MedianTotalExamRich = CALCULATE(MEDIANX(
filter(SUMMARIZE(
'BizLine','BizLine'[RSCID],"TotExam",[TotalExam]), [TotExam] >30),[TotExam]))

[JellyFishBi]

Rich, If you lived near me, you would not want for beer for quite some time.

Thank you sir.  It works as I had imagined.  I am a bit confused by the summary...Three total exams?

My gratitude.

Mike

[richbenmintz]

Hi @JellyFishBi 

added some comments to the formula

--wrapping calculate not really required
MedianTotalExamRich = CALCULATE(MEDIANX(
--filter summarized table
filter
(
--create summarized table
SUMMARIZE(
'BizLine','BizLine'[RSCID],"TotExam",[TotalExam])
--filter predicate for summarized table
, [TotExam] >30)
--second argument for the MedianX function
,[TotExam]))
 

[JellyFishBi]

Ahhh..One to Many.  Yes, it is currently One to many. 🙂