I've been running into an issue with calculating row values and total values for a measure. The measure includes calculations involving rounding and division by a parameter. Each column value for the measure is rounded appropriately after dividing, but the total row ends up a number or two off since the total row value is rounded for the sum. For the total row, I would like each row value to be rounded individually and then summed - At that point, the total row sum should be correct.
The data goes like this:
Parameter = Customers/Staff Ratio = 125 Customers/Staff
State | Customers | Current Staff | New Staff
New Staff is the measure here. New Staff can't be negative (we don't want to remove staff) - If the calculation is less than 0, then we set it to 0 (no new staff).
I set up the first measure like this:
New Staff =
ROUND((SUM(Customers[CountOfCustomers]) / 'CustomerRatio'[CustomerRatio Value]),0) - SUM(Staffing[CountOfStaff])
The result of this calculation is the calculated New Staff value based on the customer count dividided by the ratio parameter. After that, we subtract the current staff. Before rounding, this would usually be a decimal. This can be a negative or positive number.
For the table visualization, I do another measure to get a non-negative number. If the value is negative, I set it to 0 (no new staff). I am trying the HASONEFILTER method to calculate the row value and total row values separately:
New Staff NN =
IF(
HASONEFILTER(Staffing[State]),
IF(
[New Staff] > 0,
[New Staff],
0
),
CALCULATE(
[New Staff],
FILTER(Staffing,[New Staff] > 0)
)
)
The problem is with the CALCULATE function for the total row value. It is coming up 1 or 2 numbers off because of the rounding. If I leave out rounding and have decimals, it matches just fine. However, with staffing, we can't get 0.3 people, so I round 🙂
I'm thinking a SUMX is needed here for the total row, but can't seem to figure it out. Any suggestions?
