I want to build bins around transaction values, however in the data there can be multiple items in a transaction so I want to use the Total or SUM of the total transaction value as the condition ie. less than $5, $5 - $10, $10 -$15 etc. I have used a formula -
IF
[Sales Value] < 5 THEN "Less Than $5"
ELSEIF
[Sales Value] <= 10 THEN "$5-$10"
ELSEIF
[Sales Value] <= 15 THEN "$10-$15"
etc.
However I believe this is looking at each individual line item rather than the total of the transaction, anyone have any ideas?
It should work if you just use SUM() for all references to [SalesValue], such as...
if (SUM([SalesValue]) <= 5) then 'Less than $5'
elseif SUM([SalesValue]) <= 10 then '$5-$10'
elseif SUM([SalesValue]) <= 15 then '$11-$15'
else 'More than $15'
end