Hi @BrendonLayCHT,
You're problem is with the that the cell shows <Varies> and not True or Fail value?
How it the formula "if(vent % > 0.05, "True", "Fail")" supposed to work, does each item have to pass it or does the grouped value (Sum) need to pass the value.
Calculations take place on item level and with grouping just the results are compacted depending on grouping fields.
So values of "True", "True" and "Fail" result in "<Varies>", and then conditional formatting doesn't work.
The principle is that for a validation on the whole group (= Sum):
A: the whole sum = 100% (of the grouping)
B: Calculate the item value's percentage of the sum (with calculated percentage)
C: Add another calculated field and convert the item value to 100% (using the value in B)
D now each item has the total sum and will produce thesame result (True/Fail) for each grouped item.
Sample:
A: Sum = 70 => 100%
B: a Item value = 14 => 20% (use calculated percentage option with the correct groupby field)
C: calculate from the item value the total value: 14 / 20% = 70 => total sum of group
D B and C is performed for all items, so of one Grouping all items now have 70, on which the validation calc. will all return the same value and won't show "<Varies>"
Note: if the Item value = 0, this will not work, so I use a small increase to correct.
First eval the Item value: if(Vent % = 0, 0.0001, Vent %), this will cause a small inaccuracy, determine which is acceptable
Ps. a to low of a value will not work, something like 0.0001 would, but if the group would contain 100000 elements all 0 then the sum would become 10, so would be True and not Fail. This would then again need a check if it happens.
Sample with 0
A: Sum = 70 => 100%
B: a Item value = 0 => 0%
C: calculate from the item value the total value: 14 / 0% = Error and leaves a empty value!
B2: a item value = 0, => make it very small 0.001 => 0.0014...% (or something like that)
C2: 0.001 / 0.0014..% = 70 again.
I haven't been able to come up with another workaround to calculate on the sum value instead of the individual item values, would be nice if there was a setting in the Calculated values so it would calculate on the visible row and not the underlaying item values.
For the more complex calculation I created a addin that does the calculations.
Hopefully it helps, it can get complicated. Let me know if you need further help with it.
- Michel
