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 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.
Solved! Go to Solution.
I would like to delete the post.
I have 2 similarly named tables and my measure referenced the wrong one.
I would like to delete the post.
I have 2 similarly named tables and my measure referenced the wrong one.
Hi @I_Like_Pi,
You can just mark your reply as answer to close this thread.
Thanks,
Lydia Zhang
Can you attach the .pbix file?
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 |
---|---|
108 | |
98 | |
81 | |
65 | |
62 |
User | Count |
---|---|
147 | |
116 | |
104 | |
88 | |
65 |