Forum Discussion
Checking multiple columns against a variable
It's using SUMX which works one value at a time, and sums the expression given, not the raw value.
The expression says "if non-compliant, output a 1 otherwise 0". When we sum that, if it is 0 then all of the values must have been compliant.
Since you allow hours to be over, the check should be [Value] < [Standard] but the principle works.
Hi amitchandak,
I'm not sure that will work as it will produce compliance even if the columns were as follows:
| Employee | Week 1 | Week 2 | Week 3 | Week 4 | Standard |
| Jack | 40 | 42 | 38 | 40 | 40 |
The sum of the columns will average 40, even though one column is below 40.
It's using SUMX which works one value at a time, and sums the expression given, not the raw value.
The expression says "if non-compliant, output a 1 otherwise 0". When we sum that, if it is 0 then all of the values must have been compliant.
Since you allow hours to be over, the check should be [Value] < [Standard] but the principle works.
That's great, thanks amitchandak.
My mistake, I hadn't appreciated the SUMX function. This works perfectly, it's a very neat and tidy solution, I thought I'd need to do more than one operation.