Calculate Standardize to remove data points - power pivot Z-Score

[MungoSerge]

Hi all,
I'm trying to build a model using Power Pivot (data is already loaded into power query). I'm having a difficult time trying to create a power pivot measure that would return what I believe is called the Z-Score.
Over each set of data I need to calculate the average and the standard deviation, to then determine the Z-Score; which will then highlight any data points that are outside the 'normal' range within the data. I have done this previously in Excel with formula on a worksheet using AVERAGE, STDEV.P, and STANDARDIZE, but struggling to replicate in power pivot.

The image below is just a small illustration of my data from an existing query. There are several teams, dates (with associated workdays), and then the numbered columns reflect the hour and the score achieved in each hour on that date for that team. What I would like to do is select in a pivot table a date range, and then return what the z-score would be – for example based on that table it would be the z-score for each date and time, so 20 results in total – but the averages, standard deviation etc should only be calculated against that team name on that weekday and for that hour.
I’m basically trying to highlight anomalies over say a 10 week period for that day of the week at that time, and then calculate an average once I’ve removed the anomalies from the data (for that day of the week at that hour interval).
I have tried a few different approaches with measures, but nothing seems to be quite right, and I’m unsure if it isn’t the measures that are the problem, but rather how my data is organised in the query.

[Anonymous]

Hi @MungoSerge ,

The DAX language also includes functions such as AVERAGE and STDEV.P. Please refer to these links for more information.

https://learn.microsoft.com/en-us/dax/average-function-dax

https://learn.microsoft.com/en-us/dax/stdev-s-function-dax

If you need more specific assistance with the formula, as suggested by @lbendlin , it would be helpful to provide some sample data.

Best Regards,

Bof

[Omid_Motamedise]

AS you whant to calculate (x-AVG)/sd you can use the below function in Dax, if your table name is X and your data in one column namely data (it would be better to unpivot your data and show them into one column, as it is harder to operate over the columns)

Z = 

var AVG = calculate(average(X[data]),all(X)),

return (X[data] - AVG) / X[SD]

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information. Do not include anything that is unrelated to the issue or question.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...