cancel
Showing results for
Did you mean:
Frequent Visitor

## Can PowerBi give me Z-Scores

Hi,

I have a large dataset linked into powerbi which is currently creating me great dashboards.

However, is it possible for PowerBi to give me Z scores on certain variables based on others:

Example - Z-Score for Person A's total steps for today compared to a normal Thursday.

I do not have the Z values in the raw dataset - im looking for PowerBi to create it itself and then plot it in my charts.

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator

## Re: Can PowerBi give me Z-Scores

Based on my reserach, there is a function to return Z-Score directly. We need to calculated in DAX.

Mean = CALCULATE(AVERAGE(Table1[Steps]),ALLEXCEPT(Table1,Table1[WeekDayName]))

StandardDeviation = CALCULATE(STDEV.P(Table1[Steps]),ALLEXCEPT(Table1,Table1[WeekDayName]))

Z-Score column = (Table1[Steps]-Table1[Mean])/Table1[StandardDeviation]

measure

Z-Score measure =
var mean = CALCULATE(AVERAGE(Table1[Steps]),ALLEXCEPT(Table1,Table1[WeekDayName]))
var StandardDeviation = CALCULATE(STDEV.P(Table1[Steps]),ALLEXCEPT(Table1,Table1[WeekDayName]))
return (SUM(Table1[Steps])-mean)/StandardDeviation

Regards,

Charlie Liao

2 REPLIES 2
Moderator

## Re: Can PowerBi give me Z-Scores

Based on my reserach, there is a function to return Z-Score directly. We need to calculated in DAX.

Mean = CALCULATE(AVERAGE(Table1[Steps]),ALLEXCEPT(Table1,Table1[WeekDayName]))

StandardDeviation = CALCULATE(STDEV.P(Table1[Steps]),ALLEXCEPT(Table1,Table1[WeekDayName]))

Z-Score column = (Table1[Steps]-Table1[Mean])/Table1[StandardDeviation]

measure

Z-Score measure =
var mean = CALCULATE(AVERAGE(Table1[Steps]),ALLEXCEPT(Table1,Table1[WeekDayName]))
var StandardDeviation = CALCULATE(STDEV.P(Table1[Steps]),ALLEXCEPT(Table1,Table1[WeekDayName]))
return (SUM(Table1[Steps])-mean)/StandardDeviation

Regards,

Charlie Liao

Regular Visitor

## Re: Can PowerBi give me Z-Scores

thanks a lot, Charlie

you really helped me.

Announcements

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

#### Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!