If you want to separate them out then two calcs:
if [Hands on vs Hands off]="Hands Off" then [Duration] end
if [Hands on vs Hands off]="Hands On" then [Duration] end
These can then be summed, averaged, or subtracted from each other (though you would need to use an aggregation as a row is only hands on or hands off: sum([Hands on])-sum([Hands off]) for example
Thanks Alex. I originally tried that, however I need the Hands on/off to remain a dimension and then one separate measure to either return the value for "hands on" or if its hands off I need it to create a calculation for "hands off" (hands off - hands on)...
Sorry if this is confusing...
Yep, no idea what you want. How would you write it in Excel?
Not as straightforward as you would think as you need to access the sum of all the other buckets from the null group. I've used a table calc - there may be other/better ways of doing this.
if isnull(attr([CareGiver Buckets])) then sum([Duration Minutes])-
window_sum(sum(if not isnull([CareGiver Buckets]) then [Duration Minutes] else 0 end)) else sum([Duration Minutes]) end
TEST AK.twbx.zip 23.3 KB
That's it, thanks!