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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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