Summing a measure column having break based on another column

[Former Member]

Hi,

I have the following sample webI report.

Vendor No.	Cost	Status
100	1000	Y
		N
		N
		Y
200	2000	Y
300	2000	N
400	5000	Y

I want to calculate total cost for all vendors having status "Y". In the report break is applied on 'Vendor No.' and 'Cost' Column.

I have used the following formula

=Sum(Cost) In Break where (Status="Y")

But it is giving the total cost as 9000. It is summing up the two rows of "Y" for vendor "100" to 2000 rather than taking only 1000. The correct total cost value will be 8000 ie, '1000' from Vendor No. '100', '2000' from Vendor No.'200' and '5000' from Vendor No.'400'.

Please suggest a formula that will show the correct total cost.

I am using SAP BOXI R3.1 version.

Regards,

Argha

[amitrathi239]

Hi,

Can you explain the logic on which you want to exclude the second Y value for vendor 100.As per my understanding you are calculating the total cost where status is Y and 9000 is correct in this case.

And if you want to display only only one status Y for each Vendor then use below formula.

=Sum(Max([Cost])) Where ([Status]="Y") in this you will get the 8000.

Amit

[Former Member]

Hi Argha,

total value is 9000 only it will take two times 1000 from  vendor no:100

if you need 8000, ~only one measure for each vendor no than u can go for

=Sum(Max([Cost])) Where ([Status]="Y") in this you will get the 8000

it returns only max of cost from each vendor no

[Former Member]

HI,

I would use inpu context in Sum, like

SUm([Cost] Where ([Status]="Y"))

REgards,

ROgerio