Data Normalization - Z-Score

[rgadbois]

Hi,

I am attempting to figure out how to automate a column in which I am able to create a rolling average on near-daily basis, and "normalize" these numbers as a Z-score to reflect acute changes in data. The numbers I am working with are an example of below:

Thie issue I am running into is the fact that these numbers become difficult to work with as the column for names is not consistent (ie some players are missing some days). The column "Normalized" is my expected result. Any help or assistance is greatly appreciated!

[v-yanjiang-msft]

Hi @rgadbois ，

Sorry I'm not clear how the column "Normalized" is calculated. Could you please give a example to clerify? Also, in the sample you provided, all players have four days, there isn't a player missing a day.

Best Regards,
Community Support Team _ kalyj

[rgadbois]

Hi,

For this example I used the "NORMALIZE" function in Excel, which completes a Z-score calculation based on the cell value in question ("PL" in this case), as well as the average and standard deviation. 

I am seeking assistance for developing either a measure or a calculated column that can create this process for me, as I am constantly adding data that includes that above (excluding the "Normalized" function). What I am hoping to do is to have a rolling 28-day average for each player's own numbers, a standard deviation of their daily number relative to their 28-day average, and presented as a Z-score (such as that above).

Additionally, I know that each player has four days' data, and there is not a player missing a day. I simply added that information for context, as well as simplified the example I provided.

I hope this clarifies things, and any assistance you can direct me towards would be greatly appreciated!