Forum Discussion
Standard Deviation and Z-Score calculation depending on time slicer
I fixed the problem. In case anyone out there is curious, here is how I did it.
PBIX - https://1drv.ms/u/s!Ar0HG-aZJh_ZhTPiiyKo0VDhPdLa
I started with two queries, Agent_Data, and Call_Data. Agent_Data has three columns:
Name - The Agent's full name
id - The Agent's unique id
Role - Standard or NonstandardCall_Data has two Columns:
Agent - The Agent's full name
StartTime - The date and time of the inbound callUsing PowerQuery:
1. I created a new Date column in Call_Data that extracts the date from StartTime using Date.From()
2. I made a copy of Agent_Data by right-clicking>Duplicate. I called this table "Totals."
3. I removed all columns from "Totals" except for the id column.4. I added a custom Date column to "Totals" using the List.Dates() function. I called this "Date."
5. I expanded the "Date" column and changed the type to Date.
This gave me a table with each agent name listed once for every date in the range.
Using Dax:
1. I created a table called "Calendar" by using the Calendar() function. I created the following relationships:
a. Agent_Data[Name] <> Call_Data[Agent]
b. Agent_Data[id] > Totals[id]
c. Calendar[Date] > Call_Data[StartDate]
d. Calendar[Date] > Totals[Date]
2. I created a Measure M_Calls_Taken = COUNTA(Call_Data[StartDate])+0
3. I created a column 'Totals'[Calls_Taken] = M_Calls_Taken
4. I created the following additional Measures:
COUNT_Calls_Taken = calculate(DISTINCTCOUNT('Totals'[id]),allselected('Totals'))
SUM_Calls_Taken = calculate(sum('Totals'[Calls_Taken]),allselected('Totals'))+0Mean_Calls_Taken = [SUM_Calls_Taken]/[COUNT_Calls_Taken]
Var_squaredif = Power(sum('Totals'[Calls_Taken])-[Mean_Calls_Taken],2)
Var_Calls_Taken = var difsquare = SUMX(ALLSELECTED(Agent_Data),[Var_squaredif]) return difsquare/[COUNT_Calls_Taken]
StDev_Calls_Taken = sqrt([Var_Calls_Taken])
Z-Score_Calls_Taken = ([M_Calls_Taken]-[Mean_Calls_Taken])/[StDev_Calls_Taken]
This allowed me to select various dates within my range along with different roles at the report level and adjust the Z-Scores on the fly. I posted this reply immediately after fixing the issue, so there is still a bit of cleanup/consolidating to do, but hopefully the above steps will help with thinking through any similar issues.
Thank you.
https://1drv.ms/u/s!Ar0HG-aZJh_ZhTFQdh0NcfwPRxce
Above is a link to a sample PBIX file that can reproduce the issue.
My only real objective is getting the correct Z-Score values. Any way we can acheive this is open at this point.
Below are screenshots from the file above depicting what it is displaying vs what I want it to display.
Current:
All Dates&All Agents (Z-Score values are correct)
All Dates&Standard Agents (Z-Scores are correct)
Dates1/2-1/5&All Agents (Z-Scores are Incorrect)
Dates1/2-1/5&Standard Agents (Z-Scores are Incorrect)
_______________________________________________________
Desired:
AllDates&AllAgents (Same as above, this currently works)
AllDates&Standard Agents (Same as Above, this currently works)
Dates1/2-1/5&All Agents (Z-Scores are correct)
Dates1/2-1/5 & Standard Agents (Z-Scores are correct)
I understand that due to the nature of calculated column values being computed during database processing, I may not be able to use them in a date slicer. The problem is that I need to calculate the Mean and Standard Deviation over the total values per Agent and those formulas require me to feed them a column. For this reason, I am also venturing down the route of only using Measures and calculating the Mean and Standard Deviations "manually" as opposed to using their formulas. This is giving me a different issue which I will post in a separate reply.
I was able to create some measures that replace the calculated column which solves the issue with the Mean and Standard Deviation. However, the method relies on a summary table that breaks down the number of calls each Agent received by date. I created a manual table (Called Totals_Manual) in order to fix the Mean and Standard Deviation issues. Now that I have solved that issue, I need to be able to generate this table using Dax as opposed to manual input.
The table should have a list of dates with each agent name appearing once per date, then the number of calls taken on that date by that agent. If the Agent did not take any calls that day, their name would still appear, but it would have a 0 in the calls_taken column.
A sample PBIX file is here https://1drv.ms/u/s!Ar0HG-aZJh_ZhTLheBmCj5b4b_uB
Note that "Calls_Taken" is really only one of several different items for which I will need to calculate Z-Scores. I plan to use the same method for each item and add them on to the summary table as needed. I would imagine the summary table to look similar to the below table when all is said and done.
| Date | AgentId | Calls_Taken | Calls_Made | Calls_Missed | Tickets_Created | Tickets_Resolved |
| 1/1/2017 | Aatkins | 2 | 1 | 1 | 1 | 2 |
| 1/1/2017 | Bbenning | 1 | 3 | 2 | 1 | 3 |
| 1/1/2017 | Ccastor | 2 | 1 | 4 | 3 | 4 |
| 1/1/2017 | Ddurant | 0 | 4 | 4 | 1 | 2 |
| 1/1/2017 | Eesterhouse | 0 | 2 | 4 | 1 | 1 |
| 1/1/2017 | ffrankson | 0 | 2 | 0 | 4 | 2 |
| 1/1/2017 | ggoldsmith | 0 | 2 | 3 | 1 | 0 |
| 1/2/2017 | Aatkins | 1 | 3 | 1 | 0 | 2 |
| 1/2/2017 | Ddurant | 2 | 2 | 3 | 2 | 0 |
| 1/2/2017 | Bbenning | 0 | 0 | 4 | 0 | 4 |
| 1/2/2017 | Ccastor | 0 | 3 | 4 | 2 | 1 |
| 1/2/2017 | Eesterhouse | 0 | 4 | 0 | 1 | 3 |
| 1/2/2017 | ffrankson | 0 | 4 | 1 | 3 | 3 |
| 1/2/2017 | ggoldsmith | 0 | 4 | 4 | 2 | 1 |
| 1/4/2017 | Aatkins | 3 | 0 | 3 | 1 | 3 |
| 1/4/2017 | Bbenning | 2 | 2 | 3 | 3 | 2 |
| 1/4/2017 | Ccastor | 1 | 1 | 1 | 4 | 3 |
| 1/4/2017 | Eesterhouse | 1 | 0 | 3 | 1 | 0 |
| 1/4/2017 | Ddurant | 0 | 3 | 3 | 1 | 0 |
| 1/4/2017 | ffrankson | 0 | 2 | 3 | 3 | 3 |
| 1/4/2017 | ggoldsmith | 0 | 0 | 0 | 3 | 3 |
| 1/5/2017 | Aatkins | 4 | 4 | 0 | 0 | 4 |
| 1/5/2017 | Bbenning | 2 | 1 | 2 | 0 | 1 |
| 1/5/2017 | Ccastor | 2 | 3 | 4 | 0 | 0 |
| 1/5/2017 | Ddurant | 2 | 1 | 3 | 3 | 3 |
| 1/5/2017 | Eesterhouse | 1 | 2 | 1 | 4 | 0 |
| 1/5/2017 | ffrankson | 0 | 3 | 3 | 2 | 3 |
| 1/5/2017 | ggoldsmith | 0 | 3 | 2 | 0 | 0 |
| 1/8/2017 | Ccastor | 1 | 0 | 0 | 3 | 3 |
| 1/8/2017 | Ggoldsmith | 1 | 1 | 1 | 4 | 1 |
| 1/8/2017 | Aatkins | 0 | 2 | 3 | 0 | 1 |
| 1/8/2017 | Bbenning | 0 | 3 | 0 | 0 | 1 |
| 1/8/2017 | Ddurant | 0 | 0 | 2 | 1 | 3 |
| 1/8/2017 | ffrankson | 0 | 1 | 4 | 1 | 3 |
| 1/8/2017 | Eesterhouse | 0 | 3 | 2 | 0 | 1 |
Again, any help you can provide is appreciated.
Thank you!
I fixed the problem. In case anyone out there is curious, here is how I did it.
PBIX - https://1drv.ms/u/s!Ar0HG-aZJh_ZhTPiiyKo0VDhPdLa
I started with two queries, Agent_Data, and Call_Data. Agent_Data has three columns:
Name - The Agent's full name
id - The Agent's unique id
Role - Standard or NonstandardCall_Data has two Columns:
Agent - The Agent's full name
StartTime - The date and time of the inbound callUsing PowerQuery:
1. I created a new Date column in Call_Data that extracts the date from StartTime using Date.From()
2. I made a copy of Agent_Data by right-clicking>Duplicate. I called this table "Totals."
3. I removed all columns from "Totals" except for the id column.4. I added a custom Date column to "Totals" using the List.Dates() function. I called this "Date."
5. I expanded the "Date" column and changed the type to Date.
This gave me a table with each agent name listed once for every date in the range.
Using Dax:
1. I created a table called "Calendar" by using the Calendar() function. I created the following relationships:
a. Agent_Data[Name] <> Call_Data[Agent]
b. Agent_Data[id] > Totals[id]
c. Calendar[Date] > Call_Data[StartDate]
d. Calendar[Date] > Totals[Date]
2. I created a Measure M_Calls_Taken = COUNTA(Call_Data[StartDate])+0
3. I created a column 'Totals'[Calls_Taken] = M_Calls_Taken
4. I created the following additional Measures:
COUNT_Calls_Taken = calculate(DISTINCTCOUNT('Totals'[id]),allselected('Totals'))
SUM_Calls_Taken = calculate(sum('Totals'[Calls_Taken]),allselected('Totals'))+0Mean_Calls_Taken = [SUM_Calls_Taken]/[COUNT_Calls_Taken]
Var_squaredif = Power(sum('Totals'[Calls_Taken])-[Mean_Calls_Taken],2)
Var_Calls_Taken = var difsquare = SUMX(ALLSELECTED(Agent_Data),[Var_squaredif]) return difsquare/[COUNT_Calls_Taken]
StDev_Calls_Taken = sqrt([Var_Calls_Taken])
Z-Score_Calls_Taken = ([M_Calls_Taken]-[Mean_Calls_Taken])/[StDev_Calls_Taken]
This allowed me to select various dates within my range along with different roles at the report level and adjust the Z-Scores on the fly. I posted this reply immediately after fixing the issue, so there is still a bit of cleanup/consolidating to do, but hopefully the above steps will help with thinking through any similar issues.
Thank you.