Weighted Standard Deviation (SD of Hourly Rate * Hours)

[I_Like_Pi]

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

[Phil_Seamark]

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?

[Phil_Seamark]

Oh hang, on, I think I see.....

[I_Like_Pi]

Yes that works, sorry that wasn't clearer. I am currently calculating 15/2. But would prefer 35/4. Similarly I would like to calc the standard Deviation based on all 4 hours as opposed to the 2 records. Hopefully that clarifies it a bit. Yours still works when you add in the type as a grouping. Again my primary goal is to identify outliers in the original data. In type S = standard Time and O = Overtime ie time and a half.

[Phil_Seamark]

Hi @I_Like_Pi,

That does and I think my post above solves the first part. Will look at the SD part now.

[Phil_Seamark · Accepted 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])

[I_Like_Pi]

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.

[Phil_Seamark]

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