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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
maoldham19
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

 

  maoldham19_0-1674181997131.png

 

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.

4 REPLIES 4
DataInsights
Super User
Super User

@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 )

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




maoldham19_0-1674440313357.png

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

 

@maoldham19,

 

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Link Hopefully this link will get you to the file.

Helpful resources

Announcements
September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.