Forum Discussion
Standard Deviation and Z-Score calculation depending on time slicer
- 9 years ago
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.
Hi GTRailey,
What's the relationship between your Agent_Data table, Call_Data table and your Calendar table? Could you share a sample pbix file which can reproduce the issue? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.:smileyhappy:
Regards
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.