Accurate totals when repeated rows exists in the data set

[d_prashanthredd]

Hi Experts,

I'm facing an issue to get correct totals. Below is my sample data..

In above sample data, you can see opp1 repeated with the same product code but associated with different region.

I'm trying to achieve below..

Note:

1. Sockets are unique to a Product Code and it may be the same for other Products too

2. Total Quantity = Sum(Quantity)

3. Total # of Sockets = Sum(Qty * Sockets) or Sum(Qty) * Sockets

I want to get the results same as above but the total should be 12 instead 20 and the row wise values are fine.

Please help me to get that.

[niclaz79]

Hi,

By simply leaving out the dimension Opp you will get the summary on two lines.

I would use the expression Sum(Qty * Sockets) to get the result 8 and 12. But how do you come to getting the total of 8 and 12 to 12 and not 20?

[d_prashanthredd]

Row wise values are 8 and 12 which are correct.

Here my issue is, at any point of time opp1 should have 2 * 4 = 8 even if it is mapped with multiple regions.