cancel
Showing results for
Did you mean:
wsspglobal Helper I

## Calculate Z score with a single column of data that has multiple metrics

Hi,

I have a table that has a Metric Name column that consists multiple metrics and a Metric Value column. I want to calculate the z score for the data within each of the AAA, BBB, and CCC metric. As I actually have hundreds of metric to deal with in reality, I wonder if I can calculate the z score in one single column and then use a filter to look at one single metric when I built the dashboard? Or is there a better way to do it in Power BI? I guess I could also pivot the table to get three seperate columns for AAA, BBB, and CCC, but then I'll have to write the same formula three times, too tedious. Any advice? Thanks.

 Metric Name Metirc Value AAA 1.52 AAA 1.90 AAA 0.98 AAA 1.95 AAA 9.38 AAA 1.68 AAA 4.98 AAA 8.33 AAA 3.29 AAA 9.98 BBB 9.71 BBB 6.52 BBB 9.60 BBB 8.17 BBB 9.89 BBB 4.62 BBB 6.77 BBB 1.38 BBB 5.31 BBB 5.57 CCC 8.75 CCC 2.17 CCC 4.00 CCC 2.74 CCC 9.25 CCC 2.71 CCC 6.99 CCC 9.60 CCC 0.89 CCC 6.94
1 ACCEPTED SOLUTION

Accepted Solutions Super User III

## Re: Calculate Z score with a single column of data that has multiple metrics

Hi,@wsspglobal,

yes, you can:

```z score =
var _metric = CALCULATE(SELECTEDVALUE('Table'[Metric Name]))
var _sampleMean = CALCULATE(AVERAGE('Table'[Metric Value]);FILTER(ALL('Table');'Table'[Metric Name]=_metric))
var _sampleSD = CALCULATE(STDEV.S('Table'[Metric Value]);FILTER(ALL('Table');'Table'[Metric Name]=_metric))
return
DIVIDE('Table'[Metric Value]-_sampleMean;_sampleSD)```

Cheers,
Sturla

6 REPLIES 6 Super User III

## Re: Calculate Z score with a single column of data that has multiple metrics

Hi,@wsspglobal,

yes, you can:

```z score =
var _metric = CALCULATE(SELECTEDVALUE('Table'[Metric Name]))
var _sampleMean = CALCULATE(AVERAGE('Table'[Metric Value]);FILTER(ALL('Table');'Table'[Metric Name]=_metric))
var _sampleSD = CALCULATE(STDEV.S('Table'[Metric Value]);FILTER(ALL('Table');'Table'[Metric Name]=_metric))
return
DIVIDE('Table'[Metric Value]-_sampleMean;_sampleSD)```

Cheers,
Sturla Community Support

## Re: Calculate Z score with a single column of data that has multiple metrics

Hi @wsspglobal ,

You can create column or measure like DAX below.

```Column1= CALCULATE(SUM(Table1[Metric Value]),FILTER(ALLSELECTED(Table1), Table1[Metric Name] =EARLIER(Table1[Metric Name])))

Measure1= CALCULATE(SUM(Table1[Metric Value]),FILTER(ALLSELECTED(Table1),Table1[Metric Name]=MAX(Table1[Metric Name])))```

Best Regards,

Amy

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

wsspglobal Helper I

## Re: Calculate Z score with a single column of data that has multiple metrics

I can't type the last part:

`DIVIDE('Table'[Metric Value]-_sampleMean;_sampleSD)`

It seems like I cannot call out a column within DIVIDE. When I tried to call 'Table'[Metric Value], I can't. Please advise. Thank you! Super User III

## Re: Calculate Z score with a single column of data that has multiple metrics

Sorry, I forgot to mention that this would be the code for a calculated column. Are you trying to do it as a measure?

wsspglobal Helper I

## Re: Calculate Z score with a single column of data that has multiple metrics

Hi  @sturlaws,

Yes, I was. How can I do it in a measure?

Highlighted Super User III

## Re: Calculate Z score with a single column of data that has multiple metrics

```z score measure =
VAR _metric =
CALCULATE ( SELECTEDVALUE ( 'Table'[Metric Name] ) )
VAR _sampleMean =
CALCULATE (
AVERAGE ( 'Table'[Metric Value] );
FILTER ( ALL ( 'Table' ); 'Table'[Metric Name] = _metric )
)
VAR _sampleSD =
CALCULATE (
STDEV.S ( 'Table'[Metric Value] );
FILTER ( ALL ( 'Table' ); 'Table'[Metric Name] = _metric )
)
RETURN
DIVIDE (
SUMX ( VALUES ( 'Table'[Metric Value] ); SUM ( 'Table'[Metric Value] ) ) - _sampleMean;
_sampleSD
)```

Announcements #### New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community! #### ‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries. #### Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now. #### Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities. #### Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications Top Solution Authors
Top Kudoed Authors
Users online (908)