Can PowerBi give me Z-Scores

[AmanSShergill]

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

[MartynasBI]

Hi,

I have encountered a similar challenge.

I would like to calculate z scores that would be responsive to several filters/slicers in the report view, which come from different tables.

Model view:

Link to data source:

https://docs.google.com/spreadsheets/d/1aRCwOmyTxZvV5P-UobNtLOpV1B7mq1jeE3SEs-FMDRE/edit#gid=1463157...

The correct example below that I would like to get (please note for z score I use stdev.S function when calculating standard deviation):

The incorrect values I get in Power BI:

Some code examples I've tried:

1) 

ChatGPT z score =

VAR SelectedMetric = SELECTEDVALUE('Financial items'[Metric])

VAR SelectedYear = SELECTEDVALUE('Financial items'[Year])

VAR SelectedIndustry = SELECTEDVALUE(CompanyList[industry-category])

VAR Mean = CALCULATE(AVERAGE('Financial items'[Value_usd]), ALLEXCEPT('Financial items', 'Financial items'[Metric], 'Financial items'[Year], CompanyList[industry-category]))

VAR StandardDeviation = CALCULATE(STDEV.S('Financial items'[Value_usd]), ALLEXCEPT('Financial items', 'Financial items'[Metric], 'Financial items'[Year], CompanyList[industry-category]))

VAR SelectedValueUSD = SELECTEDVALUE('Financial items'[Value_usd])

RETURN

   IF(ISBLANK(SelectedMetric) || ISBLANK(SelectedYear) || ISBLANK(SelectedIndustry) || ISBLANK(SelectedValueUSD),

        BLANK(),

        (SelectedValueUSD - Mean) / StandardDeviation

    )

2) 

Measure z score =

VAR SelectedMetric = SELECTEDVALUE('Financial items'[Metric])

VAR SelectedYear = SELECTEDVALUE('Financial items'[Year])

VAR SelectedIndustry = SELECTEDVALUE(CompanyList[industry-category])

VAR Mean = CALCULATE(AVERAGE('Financial items'[Value_usd]), ALLEXCEPT('Financial items', 'Financial items'[Metric], 'Financial items'[Year]),ALLEXCEPT(CompanyList,CompanyList[industry-category]))

VAR StandardDeviation = CALCULATE(STDEV.P('Financial items'[Value_usd]), ALLEXCEPT('Financial items', 'Financial items'[Metric], 'Financial items'[Year]), ALLEXCEPT(CompanyList,CompanyList[industry-category]))

VAR SelectedValueUSD = SELECTEDVALUE('Financial items'[Value_usd])

RETURN

   IF(ISBLANK(SelectedMetric) || ISBLANK(SelectedYear) || ISBLANK(SelectedIndustry) || ISBLANK(SelectedValueUSD),

        BLANK(),

        (SelectedValueUSD - Mean) / StandardDeviation

    )

Thank you in advance

[v-caliao-msft · Accepted solution]

@AmanSShergill,

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

[Tatengue_Cau]

Hi, i'm tryin this, buy i have a error, can you help me?

z-sore =

var mean = CALCULATE(AVERAGE('DB'[ACEL ]), ALLEXCEPT('DB', 'DB'[JUGADOR]),

var sd = CALCULATE(STDEV.P('DB'[ACEL ]), ALLEXCEPT('DB', 'DB'[JUGADOR])

    return(SUM('DB'[ACEL ] - 'DB'[mean]) / 'DB'[sd])

La sintaxis de "return" no es correcta. 

[MdJ83]

Why do you include the allexept statement? What does that do? Do you need that when you look at it for an entire population without any comparison to determine the z-score for each item?

[nadavsn]

thanks a lot, Charlie 

you really helped me.