1

I have a data with columns called Brand, Market and Target. The Brand having members like A, B, C & D repeats across 2 kinds of Market - OPL and CPL. So the Target for each of Brand and Market also repeats in column Target, as in below image:

enter image description here

All I would want is to get sum of Target for each of Brand at OPL (highlighted in orange) and sum of Target for each of Brand at CPL (highlighted in yellow)

So sum for OPL should be 10000 + 7000 + 4000 + 9000 = 30000 and sum for CPL should be 5000 + 2000 + 8000 + 3000 = 18000

I used a logic like

IF [Market] = 'OPL' THEN {FIXED [Brand]+[Market] : MIN([Target])} END

Image as below:

enter image description here

which gave me total of target at OPL which is 58000, which is not the one I wanted. Please help me with correct calculation, do let me know for any further details.

feb
  • 79
  • 1
  • 1
  • 8
  • A OPL has two rows and D OPL has three rows. Yet you only highlighted the first row in orange. So those extras row are getting included in your LOD. Based on your requirement description ("sum of Target for each of Brand at OPL"), this is working correctly. I'm guessing though, that you don't want the extra rows included. Can you clarify your requirements? – Sam M Dec 06 '17 at 20:03
  • Sam, I basically need sum of unique records which means, A OPL has two rows but I need to pick only one row target similarly for D OPL since the sum of repeated rows does not answer my requirement. – feb Dec 07 '17 at 07:53

1 Answers1

7

Try this:

SUM( { FIXED [Brand], [Market]: AVG(Target) } )

Averaging within each Market and Brand will eliminate the duplicates in your sum.

Andrew LaPrise
  • 3,373
  • 4
  • 32
  • 50