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

[maoldham19]

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 - LowerQuartile
VAR OutlierThresholdLower = LowerQuartile
 - InterQuartileRange * 1.5
VAR OutlierThresholdUpper = UpperQuartile
 + InterQuartileRange * 1.5
Return

??

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.

[DataInsights]

@maoldham19,

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 )

[maoldham19]

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

[DataInsights]

@maoldham19,

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

[maoldham19]

Link Hopefully this link will get you to the file.