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

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes! Register now.

Reply
bhmiller89
Helper V
Helper V

Calculating Outliers using Measures

I wrote a bunch of calculated measures to ultimately get to a measure to show whether or not a value is within range (not an outlier) but when I go to use it as a slicer, all I get is a blank.

 

SumEHR = SUM('dpmgr vwJMWebJobSummary'[ActualEHR])

AvgEHR = AVERAGE('dpmgr vwJMWebJobSummary'[ActualEHR])

MaxEHR = MAX('dpmgr vwJMWebJobSummary'[ActualEHR])

 

Next:

 

EHR Rank = IF(ISBLANK([SumEHR]), BLANK(), RANKX(ALLSELECTED(ActiveProjects[Job#]), [SumEHR],0))   <-- maybe I messed up here?

 

Job Count for Outlier = CALCULATE(DISTINCTCOUNT(ActiveProjects[Job#]), ALLSELECTED('ActiveProjects'), NOT(ISBLANK('dpmgr vwJMWebJobSummary'[ActualEHR])))

 

Outlier Count = ROUND([Job Count for Outlier] * .05, 0)

 

Lower Outlier Rank = [Job Count for Outlier] - [Outlier Count]

 

IsInRange = IF(ISBLANK([SumEHR]), BLANK(), IF([EHR Rank] > [Outlier Count] && [EHR Rank] < [Lower Outlier Rank], 1, 0))

 

 

Any help is appreciated 

 

 

 

3 REPLIES 3
v-haibl-msft
Microsoft Employee
Microsoft Employee

@bhmiller89

 

Could you please describe more clearly about your tables and which column you use it as a slicer?

 

Best Regards,

Herbert

I plan on using "Is In Range" as a slicer to only show Projects that have EHRs that are not considered outliers.

@bhmiller89

 

If I understand you correctly, “IsInRange” is a measure and you want to put it into Visual level filters, right?

What about the result if you change following two measures?

 

SumEHR = 
CALCULATE (
    SUM ( 'dpmgr vwJMWebJobSummary'[ActualEHR] ),
    ALLEXCEPT ( 'dpmgr vwJMWebJobSummary', 'dpmgr vwJMWebJobSummary'[Job#] )
)
EHR Rank = 
IF (
    ISBLANK ( [SumEHR] ),
    BLANK (),
    RANKX ( ALL ( 'dpmgr vwJMWebJobSummary'[Job#] ), [SumEHR] )
)

 

Best Regards,

Herbert

 

Helpful resources

Announcements
September Power BI Update Carousel

Power BI Monthly Update - September 2025

Check out the September 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

Top Solution Authors
Top Kudoed Authors