Forum Discussion
SUM rounded numbers
I need to adjust my pwd calculation
By law here in my country, factories need 5% of employees to be pwd.
Until then the formula below was working normally, and everyone was happy:
PWD HC Goal = [Total HC] *5/100
[Total HC] is a simple COUNTROWS from the employee table.
However, I've been told that fractions should always be rounded up, and this is causing a problem in the formula when we try to see the grand total.
| Factory | Total HC | 5% | RoundUP | |
| 1 | 85 | 4,25 | 5 | |
| 2 | 139 | 6,95 | 7 | |
| 3 | 136 | 6,8 | 7 | |
| 4 | 209 | 10,45 | 11 | |
| 5 | 36 | 1,8 | 2 | |
| 6 | 213 | 10,65 | 11 | |
| 7 | 7 | 0,35 | 1 | |
| 8 | 167 | 8,35 | 9 | |
| 992 | 49,6 | 53 | SUM | |
| 50 | RoundUP |
For each factory, the formula PWD HC Goal = ROUNDUP([Total HC] *5/100,0) works.
However, if I try to see the total, the final number is not the sum of the rounded numbers, but the rounding of the summed numbers. Which logically makes sense, but it's not what I need.
How can I adjust this?
Best Regards,
5 Replies
Anonymous , Try using
PWD HC Goal =
SUMX(
ADDCOLUMNS(
'EmployeeTable',
"RoundedPWDGoal", ROUNDUP([Total HC] * 5 / 100, 0)
),
[RoundedPWDGoal]
)You need to force the total to work out the rounding at the factory level. You can do that with something like
Round Up = SUMX ( VALUES ( 'Table'[Factory] ), ROUNDUP ( [Total HC] * 5 / 100, 0 ) )Hi Anonymous ,
Just wanted to check if you had a chance to review the solutions provided by the super users?
If the reponses had addressed your query, please accept as solution or give Kudos so that other users can benefit from it.
Thank you
