0 votes
1 view
in Data Science by (13.5k points)

Dataset Description:

Columns: Brand, Market and Target.

Following dataset represent the target for four different brands A,B, C & D which are distributed among OPL & CPL market.

enter image description here

What I want?

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)

For example, 

sum for OPL should be 10000 + 7000 + 4000 + 9000 = 30000 and,

sum for CPL should be 5000 + 2000 + 8000 + 3000 = 18000

What I did?

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

Image as below:

enter image description here

The logic gave me total of target at OPL which is 58000,( which is not the one I wanted).

Can someone help me with the calculation part?

1 Answer

0 votes
by (33.1k points)

Simply try this method:

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

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

Hope this answer helps you!

Related questions

0 votes
0 answers
asked Aug 27 in BI by Vaibhav Ameta (15.1k points)
0 votes
1 answer
0 votes
1 answer
Welcome to Intellipaat Community. Get your technical queries answered by top developers !