Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am trying to get a standard deviation of the hourly rate per employee, but I want each hour to stand alone in that calculation.
I was so proud of my SD Calc
Emp_SDRate = CALCULATE(STDEV.S(Tbl[Rate]),ALLEXCEPT(Tbl,Tbl[EMP],Tbl[Type]))
until I realized it was considering each row equally, I pretty much broke my brain get here so making this next leap I came directly to the forum.
I also want to use it to identify records where the rate is an oultlier (>Avg+-2*SD)
ie
EMP Type Hours Rate
Bob S 3 10/hr
Bob S 1 5/hr
Doug S 3 10/hr
Doug O 1 15/hr
Calculates an average of 35/4 = 8.75 instead of 15/2 = 7.5
and the associated SD.
To do the Average I think I am going to Sum the Rate / Sum of hours. but for SD... !@#$%! I am deaf dumb and blind.
I am off in 5 so I will pondering over the evening and hopefully I get some nibbles from the community.
Thanks
Solved! Go to Solution.
Hi @I_Like_Pi
This new calculated table fleshes the data out to 1 row per hour which makes calcuating the STD easier
Table 2 = VAR T1 = SELECTCOLUMNS(CALENDAR(1,100),"n",int([Date])) RETURN SELECTCOLUMNS( ADDCOLUMNS( FILTER( CROSSJOIN(Tbl,T1), [Hours]>=[n] ), "H",1), "Emp",[Emp], --"Hour",[H], "Rate",[Rate] )
You can then create a SD measure on the new table
SD = STDEV.S([Rate])
Hi @I_Like_Pi,
When you say "Calculates an average of 35/4 = 8.75 instead of 15/2 = 7.5", I'm guessing you are only talking about Bob and that the 35 comes from the Hours * Rate (3 x 10 and 1 x 5) to ge the 35 and this is being divided by 4 total hours.
If this is the case, I'm not so clear on where the "15/2 = 7.5" comes from. Does this still just relate to Bob?
Oh hang, on, I think I see.....
Hi @I_Like_Pi,
That does and I think my post above solves the first part. Will look at the SD part now.
Hi @I_Like_Pi
This new calculated table fleshes the data out to 1 row per hour which makes calcuating the STD easier
Table 2 = VAR T1 = SELECTCOLUMNS(CALENDAR(1,100),"n",int([Date])) RETURN SELECTCOLUMNS( ADDCOLUMNS( FILTER( CROSSJOIN(Tbl,T1), [Hours]>=[n] ), "H",1), "Emp",[Emp], --"Hour",[H], "Rate",[Rate] )
You can then create a SD measure on the new table
SD = STDEV.S([Rate])
Thanks Phil,
This will work great for both Employee and job title.
... So what happens to all the half hours (my example didn't make that clear)? I assume they drop. I may prep this with a summarization of the hours before I feed it to your break out, that way all the half and quarter hours aggregate beforehand. Dropping or adding only a single hour per employee.
I think to achieve the first part simply add a calculated column to your table
Total = Tbl[Hours] * Tbl[Rate]
Which should give you a table that looks like this
Then on your table if you add the following measure (format to 2 decimal places)
Average Rate = DIVIDE( CALCULATE(SUM('Tbl'[Total])), CALCULATE(SUM('Tbl'[Hours])) )
You can create a grid
If this is on the right track, let me know and we can deal with the SD stuff
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |