Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
Try this one
MedianTotalExamRich = CALCULATE(MEDIANX( filter(SUMMARIZE( 'BizLine','BizLine'[RSCID],"TotExam",[TotalExam]), [TotExam] >30),[TotExam]))
Proud to be a Super User!
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
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:
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:
The Results of this are this:
Filtering >30 Returns a null column. Any ideas would be helpful.
Thanks for your response.
Mike
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!
Proud to be a Super User!
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
Hi @JellyFishBi ,
Can you provide an anonymized version of your .PBIX would allow me to get the correct formula for you
Proud to be a Super User!
Try this one
MedianTotalExamRich = CALCULATE(MEDIANX( filter(SUMMARIZE( 'BizLine','BizLine'[RSCID],"TotExam",[TotalExam]), [TotExam] >30),[TotExam]))
Proud to be a Super User!
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
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]))
Proud to be a Super User!
Ahhh..One to Many. Yes, it is currently One to many. 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
98 | |
80 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |