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

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

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Fabric Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.