cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

## Determine if variable Greater than (lesser than) group upper (lower bound) for the ground

I am have an issue in replciating an Excel sheet in Power BI. The background is I have a matrix of data (Metrics as Columns, Companies as Rows, and the Metric values as values. Therefore, my orignal matrix looks like this

I have been successful in calculating the upper and lower bounds for the data using the following code. (Taken from here basically https://stackoverflow.com/questions/66899642/calculating-percentiles-by-group-in-power-bi)

`Outlier = VAR SelectedUnit =  sum(ESG_Data[Metric_Value])VAR LowerQuartile = PERCENTILEX.INC (  ESG_Data, ESG_Data[Metric_Value], 0.25 )VAR UpperQuartile =  PERCENTILEX.INC ( ESG_Data, ESG_Data[Metric_Value], 0.75 )VAR InterQuartileRange = UpperQuartile - LowerQuartileVAR OutlierThresholdLower = LowerQuartile - InterQuartileRange * 1.5VAR OutlierThresholdUpper = UpperQuartile + InterQuartileRange * 1.5Return??`

I can reconcile the values of the OutlierThresholdUpper and OutlierThresholdLower when I return them with the calculations in Excel. Next I want to count for each row (the tickers) how many times they have a variable which is an outlier and how many variables are within the upper and lower bounds.

When I try an IF statement like below (I could not get the SWITCH approach to work)

`Return if ( SelectedUnit <= OutlierThresholdLower  || SelectedUnit >= OutlierThresholdUpper, "Outlier", "Ok" )`

The result suggests all metrics for all firms are outliers. The issue no dount relates to the SelectedUnit variable. What I cannot get to work is getting Power BI to compare the Measure with orginal values for each row and column.

4 REPLIES 4
Super User

Try wrapping the table in each PERCENTILEX.INC function in ALLSELECTED. This will remove the filter context for a specific row (ticker) in the matrix, using only filters external to the visual. Example:

``````VAR LowerQuartile =
PERCENTILEX.INC ( ALLSELECTED ( ESG_Data ), ESG_Data[Metric_Value], 0.25 )``````

Proud to be a Super User!

Frequent Visitor

I did try this and above is the result when I return the upper outlier. Obivously not what I was hoping, I need the values in each particlar colum to be the same. Its like I need an allexcept but I cannot get the syntax to work

Super User

If you could share your sanitized pbix using one of the file services like OneDrive, that would help.

Proud to be a Super User!

Frequent Visitor

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors