Individualised Z-Score and % of Mean for Time Series Data within groups

[Deanblittle]

Hi Guys,

I am currently working with Power BI to handle and report daily data to take over older excel reporting systems.

I have an issue that I am trying to solve that is confusing me due to filtering. I work with a group of individuals and collect a large number of variables on each of them daily. We have interests in both how the groups and sub-groups perform but also how each individual is performing relative to their historical data.

For reporting purposes, we want to be able to reflect the significance of a particular variable, for a given date for an individual against that individuals average over a period of time (i.e.; month, quarter, year?). I have tried numerous ways to handle this and keep running into similar issues each time (i.e.: daily filter also filters the average or z-score to that date). 

Your assistance would be greatly appreciated, I am sure I am repeatedly making a simple mistake.

Thank-you

[v-yulgu-msft]

Hi @Deanblittle,

Please refer to this blog to provide more detailed and useful information so that I can reproduce the same scenario on my side.

Regards,

Yuliana Gu

[Deanblittle]

I have completed part of the process and I have converted all of the averages into separate tables (1= all time, 2 = month).

The next step is where I am struggling. That is that I need a way to connect the raw daily data on the sheet with the average in the table in an equation (% of monthly Average = calculate(Divide(sheet1[dailyscore], table[averagescoremonth]).

This approach works for one variable. But I have 17 categorical variables (i.e.: Person 1, Person 2, Person 3.... and so on) that I want to express how their daily score reflects against their own daily average for the last month on a dashboard.

Thanks again.