If I want to select [NewField2] and look for values 'CARBRAND' then pick corresponding value from [Spend] and ADD that to where [Newfield2] value is 'PUREBRAND' and the value from [Media..] Highlighted in attachment
Nicci: Your formulas need to evaluate row by row. If you are are testing for [Newfield2]='CARBRAND' then you cannot also test for [Newfield2]='PUREBRAND' ...both things can never be true for the same row, unless you want to tokenise the field and permit multiple values per cell...?
SUBSET functions can process groups of rows with the same value in a given field, but in your data set, you have logic trying to group more than one value for [Newfield2].
I think we need to do some more pre-processing to the data set such that you do not need to test [Newfield2] twice for different values?
I have created few extra column to avoid such thing and guys in work seems ok so far.. This callc actually shows them overall comparison and really want this in the report.
Your Field organiser formula is adding values of 2 cells, from fields [Total spend] and [Media spend] in the same row. A 'vertical' sum is performed when you aggregate data.
If you wish to have different total for each data subset, depending on the value of another field, e.g. to calculate sum for each data Category, then you need to use subset formulas, such as:
SUBSET_SUM([Test], SUBSET([Category]))
The sum of the [Test] field, for the data subset with the same [Category] field value as the record being evaluated.
The formula needed here was IF([New field2]="PureBrand", SUBSET_SUM ([Spend],SUBSET([New field2],"CarBrand"))+ SUBSET_SUM ([Media Spend Car],SUBSET([New field2],"PureBrand")), null)