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.