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
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.
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!
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
If you could share your sanitized pbix using one of the file services like OneDrive, that would help.
Proud to be a Super User!
User | Count |
---|---|
117 | |
94 | |
87 | |
75 | |
64 |
User | Count |
---|---|
138 | |
112 | |
110 | |
98 | |
94 |