I have some payroll data:
Office Employee Month Wk Hrs Gross
A 123 JAN 1 40 2000
A 123 JAN 2 40 2000
A 321 JAN 1 35 2200
A 321 JAN 2 35 2200
B 789 JAN 1 40 2500
B 789 JAN 2 38 1800
B 987 JAN 1 35 1600
B 987 FEB 3 32 1450
...
I built 3 measures:
M_Tot_Hrs = SUM(TABLE[Hrs])
M_Tot_Gross = SUM(TABLE[Gross])
M_Avg_Rate = M_Tot_Gross / M_Tot_Hrs
I made a cross tab (matrix) table to compare the average Rate per Office per month but my average is calculating based on the whole table and not the context related to the cell so every office and every month in the cross tab are showing the same value.
Office JAN FEB ...
A 49.65 49.65 49.65 ...
B 49.65 49.65 49.65 ...
I saw a post that noted using the Divide function with the sums embedded but I got the same result.
M_Avg_Rate = DIVIDE(
SUM(TABLE[Gross]),
SUM(TABLE[Hrs]))
... NOPE
At least if I drop the either of the other total measures in for the Matrix/Cross tab values, they are similarly calculating the full table totals and not the Office/Month total, so it is consistent I just don't understand why. Its as thou I have overwritten the context with All(Table1).
Any help is appreciated Thanks.
