Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

To celebrate FabCon Vienna, we are offering 50% off select exams. Ends October 3rd. Request your discount now.

Reply
Rdata
Advocate I
Advocate I

Calculate and Assign Z scored based on multiple criteria to show Outliers in Table Visual

‎09-29-2022 01:37 PM

I'm trying to find a way to visualize in a table when a data point is an outlier, for people to go and check it. I'm thinking perhaps a z-score column with conditional formatting is my best bet, but I'm having a little trouble conceptualizing how to calculate a z score when I need to group the data by multiple criteria. 

 

I need to group the data by TRT_ID, Metric Type, and Completed = Yes for each Z-score. So... for row 1, I'd need (8566 - Mean of "Yes" Electricity for TRT 19) / (Stand.Dev. of "Yes" Electricity for TRT 19), then for that to cascade down through the different TRT_ID and Metric_Type combinations. I can't seem to wrap my brain around how to write that... and whether or not it should be a custom column or a measure? 

 

Sample data in post below because of character limit issues

 

Labels:
Message 1 of 6
1,360 Views
0
1 ACCEPTED SOLUTION
Rdata
Advocate I
Advocate I

‎10-14-2022 11:48 AM

I solved my own problem with three measures: 

 

Average = CALCULATE(
    AVERAGE ( Metrics_Outlier[Use] ),
    ALLEXCEPT ( Metrics_Outlier, Metrics_Outlier[TRT_ID], Metrics_Outlier[Metric_Type] )
)

 

STDev = CALCULATE(
    STDEVX.P( Metrics_Outlier, Metrics_Outlier[Use] ),
    ALLEXCEPT ( Metrics_Outlier, Metrics_Outlier[TRT_ID], Metrics_Outlier[Metric_Type] )
)
 
Zscore = (SUM(Metrics_Outlier[Use])- [Average])/[STDev]
 
Rdata_0-1665773266242.png

 

 

View solution in original post

Message 6 of 6
1,259 Views
0
5 REPLIES 5
Rdata
Advocate I
Advocate I

‎10-14-2022 11:48 AM

I solved my own problem with three measures: 

 

Average = CALCULATE(
    AVERAGE ( Metrics_Outlier[Use] ),
    ALLEXCEPT ( Metrics_Outlier, Metrics_Outlier[TRT_ID], Metrics_Outlier[Metric_Type] )
)

 

STDev = CALCULATE(
    STDEVX.P( Metrics_Outlier, Metrics_Outlier[Use] ),
    ALLEXCEPT ( Metrics_Outlier, Metrics_Outlier[TRT_ID], Metrics_Outlier[Metric_Type] )
)
 
Zscore = (SUM(Metrics_Outlier[Use])- [Average])/[STDev]
 
Rdata_0-1665773266242.png

 

 

Message 6 of 6
1,260 Views
0
Rdata
Advocate I
Advocate I

‎10-04-2022 10:45 AM

I still don't have a solution to this, the one provided above doesn't seem to be giving the right numbers. 

I am playing around with pivoting the table with the metric types as separate columns so that the data only has to be grouped by TRT_ID & Completed = Yes . There would then be a z-score column for each metric type. Below is a sample of just electricity; I figure it would be the same for all the other metrics, just changing the columns that the use data is being pulled from.

 

Sample Table: 

 

TRT_IDDateElectricity UseElectricity Use CompleteElectricity Z-Score
19January, 20228566Yes 
19February, 20228052Yes 
19March, 20229334Yes 
19April, 20228681Yes 
19May, 20227529Yes 
19June, 20229166Yes 
19July, 20229350Yes 
19August, 202211342Yes 
19September, 2022 No 
22January, 20227405.86Yes 
22February, 20227166.25Yes 
22March, 202212397.47Yes 
22April, 20225691.83Yes 
22May, 20226619.3Yes 
22June, 20225988.94Yes 
22July, 20226640.56Yes 
22August, 20226154.99Yes 
22September, 2022 0No 

 

 

 

Message 5 of 6
1,273 Views
0
Rdata
Advocate I
Advocate I

‎09-29-2022 01:47 PM
TRT_IDMetric_TypeDateUseCompletedUseZ-score
19ElectricityJanuary, 20228566Yes 
19ElectricityFebruary, 20228052Yes 
19ElectricityMarch, 20229334Yes 
19ElectricityApril, 20228681Yes 
19ElectricityMay, 20227529Yes 
19ElectricityJune, 20229166Yes 
19ElectricityJuly, 20229350Yes 
19ElectricityAugust, 202211342Yes 
19ElectricitySeptember, 2022 No 
19Natural GasJanuary, 2022166.72Yes 
19Natural GasFebruary, 2022138.16Yes 
19Natural GasMarch, 2022338.44Yes 
19Natural GasApril, 202247.12Yes 
19Natural GasMay, 202210Yes 
19Natural GasJune, 20220.36Yes 
19Natural GasJuly, 20220.71Yes 
19Natural GasAugust, 20220.36Yes 
19Natural GasSeptember, 2022 No 
22ElectricityJanuary, 20227405.86Yes 
22ElectricityFebruary, 20227166.25Yes 
22ElectricityMarch, 202212397.47Yes 
22ElectricityApril, 20225691.83Yes 
22ElectricityMay, 20226619.3Yes 
22ElectricityJune, 20225988.94Yes 
22ElectricityJuly, 20226640.56Yes 
22ElectricityAugust, 20226154.99Yes 
22ElectricitySeptember, 2022 No 
24ElectricityJanuary, 20223935Yes 
24ElectricityFebruary, 20224557Yes 
24ElectricityMarch, 20224121Yes 
24ElectricityApril, 20223883Yes 
24ElectricityMay, 20224204Yes 
24ElectricityJune, 20224215Yes 
24ElectricityJuly, 20224143Yes 
24ElectricityAugust, 20224115Yes 
24ElectricitySeptember, 2022 No 
26ElectricityJanuary, 20225817Yes 
26ElectricityFebruary, 20225583Yes 
26ElectricityMarch, 20226083Yes 
26ElectricityApril, 20226072Yes 
26ElectricityMay, 20226430Yes 
26ElectricityJune, 20226552Yes 
26ElectricityJuly, 20227245Yes 
26ElectricityAugust, 20227785Yes 
26ElectricitySeptember, 2022 No 
27ElectricityJanuary, 20226791Yes 
27ElectricityFebruary, 20229008Yes 
27ElectricityMarch, 20227221Yes 
27ElectricityApril, 20228333Yes 
27ElectricityMay, 20228807Yes 
27ElectricityJune, 20228843Yes 
27ElectricityJuly, 20228905Yes 
27ElectricityAugust, 20229012Yes 
27ElectricitySeptember, 2022 No 
30ElectricityJanuary, 20223886Yes 
30ElectricityFebruary, 20224234Yes 
30ElectricityMarch, 20224609Yes 
30ElectricityApril, 20223751Yes 
30ElectricityMay, 20224288Yes 
30ElectricityJune, 20224286Yes 
30ElectricityJuly, 20225234Yes 
30ElectricityAugust, 20225055Yes 
30ElectricitySeptember, 2022 No 
34ElectricityJanuary, 20228534Yes 
34ElectricityFebruary, 20227708Yes 
34ElectricityMarch, 20226962Yes 
34ElectricityApril, 20226737Yes 
34ElectricityMay, 20226737Yes 
34ElectricityJune, 20226520Yes 
34ElectricityJuly, 20226737Yes 
34ElectricityAugust, 20226737Yes 
34ElectricitySeptember, 2022 No 
34WaterJanuary, 20222.04Yes 
34WaterFebruary, 20221.84Yes 
34WaterMarch, 20222.04Yes 
34WaterApril, 20221.97Yes 
34WaterMay, 20221.97Yes 
34WaterJune, 20221.91Yes 
34WaterJuly, 20221.97Yes 
34WaterAugust, 20221.97Yes 
34WaterSeptember, 2022 No 
41ElectricityJanuary, 20221852Yes 
41ElectricityFebruary, 20223142Yes 
41ElectricityMarch, 20221626Yes 
41ElectricityApril, 20221886Yes 
41ElectricityMay, 20221649Yes 
41ElectricityJune, 20221070Yes 
41ElectricityJuly, 20221295Yes 
41ElectricityAugust, 20221419Yes 
41ElectricitySeptember, 2022 No 
50ElectricityJanuary, 20229590Yes 
50ElectricityFebruary, 20226835Yes 
50ElectricityMarch, 20226064Yes 
50ElectricityApril, 20225126Yes 
50ElectricityMay, 20226359Yes 
50ElectricityJune, 20225432Yes 
50ElectricityJuly, 20224858Yes 
50ElectricityAugust, 20224511Yes 
50ElectricitySeptember, 2022 No 
54ElectricityJanuary, 20226588Yes 
54ElectricityFebruary, 20224968Yes 
54ElectricityMarch, 20225310Yes 
54ElectricityApril, 2022 No 
54ElectricityMay, 202212744Yes 
54ElectricityJune, 20228946Yes 
54ElectricityJuly, 20229252Yes 
54ElectricityAugust, 202211196Yes 
54ElectricitySeptember, 202212348Yes 
Message 2 of 6
1,322 Views
0
v-chenwuz-msft
Community Support
Community Support
In response to Rdata

‎09-30-2022 02:48 AM

Hi @Rdata ,

 

You can try this code to calcualte you want.

Z-score =
VAR _f =
    FILTER(
        ALLEXCEPT( 'Table', 'Table'[TRT_ID], 'Table'[Metric_Type] ),
        [Completed] = "yes"
    )
VAR _avg =
    AVERAGEX( _f, [Use] )
VAR _s =
    STDEVX.P( _f, [Use] )
RETURN
    DIVIDE( MAX( 'Table'[Use] ) - _avg, _s )

Result:

 

vchenwuzmsft_0-1664530829027.png

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Calculate and Assign Z scored based on multiple criteria to show Outliers in Table Visual.pbix
Message 3 of 6
1,304 Views
0
Rdata
Advocate I
Advocate I
In response to v-chenwuz-msft

‎10-03-2022 01:44 PM

I am not sure if this is right, following my requirements of grouping by TRT_ID --> Year --> Metric Type. I did a manual run of the numbers in excel and the z-score in the table for electricity & 2022/1/1 should be -.40929, not .9816

 

Rdata_1-1664829844577.png

 

Message 4 of 6
1,279 Views
0

Helpful resources

Announcements
September Power BI Update Carousel

Power BI Monthly Update - September 2025

Check out the September 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

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

View All
Top Solution Authors
View All
Top Kudoed Authors
View All