Create a sum on a BW hierarchy

[Former Member]

Hi all,

We have a report that works with a BW hierarchy.  My end-user is willing to have a sum at the bottom of his page, showing him the total number of products sold in a certain month.  Right here you can find my problem explained in a small table.

SAP BW HIERARCHY	Quantity Sold
Europe	1000
Belgium	300
Flanders	200
Wallonia	100
France	700
Asia	5000
Middle-East	2000
Saudi-Arabia	1000
Jemen	1000
China	3000
SUM:	14300

I would like to have the same table, but with a correct sum as shown in the table below

SAP BW HIERARCHY	Quantity Sold
Europe	1000
Belgium	300
Flanders	200
Wallonia	100
France	700
Asia	5000
Middle-East	2000
Saudi-Arabia	1000
Jemen	1000
China	3000
SUM:	6000

I have found in a guide of available functions in BI 4 on FP 3, that you can use a member set in any aggregate function.  It is not clear to me what is meant by that hierarchy.  Is it a BW hierarchy or not?
Whenever I try adding the member set in my calculation, nothing changes (in the wordt case, I get a #MULTIVALUE), which isn't helping me any further.

Is there someone who can explain me how to create a sum as shown in the secend table?

[Former Member · Accepted solution]

Hi,

one solution is following formula:

=Sum([Quantity Sold]) where ([Hierarchy]="Europe" Or [Hierarchy]="Asia")

this one should also work:

=Sum(If([Hierarchy].Depth=0;[Quantity Sold];0))

Best regards,

Victor