Former Member

### How to calculate the sum but ignore some variable in Webi

Hi all,

I'm using BusinessObjects BI Platform 4.1 to work on some objects.

I have some data about the sales qty. The user want to create a variable called "Sales Group" base on the qty first and do some further calculation based on this group. The original data looks like:

Store, Color, Qty

1, Red, 200

1, Black, 100

2, Red, 50

2, Black, 100

3, Red, 100

3, Black, 100

`[Sales Group] = If [Qty] ForEach ([Store])>200 then "A" else "B"`

This code works well and I can get tables below.

Store, Group

1, A

2, B

3, B

But then I met some trouble. So I want to calculate the sum of each group and add the sum into the table like:

Store, Group, Group Sum

1, A, 300

2, B, 350

3, B, 350

I tried both

```[Group Sum]=Sum([Qty]) ForEach ([Group])
[Group Sum]=Sum([Qty]) In ([Group])```

But both give me a table:

Store, Group, Group Sum

1, A, 300

2, B, 150

3, B, 200

Do anyone has any idea about how to figure this out?

Best Regards

10|10000 characters needed characters exceeded

• Jul 17, 2017 at 08:28 PM

use this formula.

=If([Group]="A") Then [Qty] Else [Qty] Where ([Group]="B") In Block