SAP BI/BW - Calculate the total on individual Columns on BEX
I have a situation using BEX query.
The case is as follows, I have several customers for different type of products.
|Customer 1||Customer 2||Customer 3|
I need the total of products (18, 19, 41) on a calculated key figure for each customer to be used in another formula.
It should look like this:
The issue I am having is that I am unable to get the total in the KFTotal key figure for each individual customer.
Until now, I have tried:
- Use exception aggregate in a formula with option “Summation” in reference to customer.
The issue here is that the KFTotal Keyfigure displays the individual value for each Product type instead of the total.
- Use Data function SUMGT on the values for each customer. The issue here is that I am having the total for all customers instead of each individual customer. I.e, all KFTotal will display 78 (18+19+41). I cannot use exception aggregate while using SUMGT.
- Use Data function SUMRT on the values for each customer. Same issue as with SUMGT.
- Use Data function SUMCT on the values for each customer. Values for KFTotal = X. I cannot work with this.
Can anybody please help me . How can I calculate the individual totals? I basically need the same total calculated locally for display in a calculated key figure.
bernhard eigelsreiter replied
I am a bit confused about your last answer.
Instead of using a calculated keyfigure for "Sum of Sales" I would suggest to try the following approach:
- For"Sum of Sales" create a keyfigure selection (or a restricted keyfigure) and:
- Enter "Sales" as keyfigure
- Add characteristic product type -> right click -> flag constant selection
- Add the same restriction on product type as defined globally (e.g. restrict on the same variable which appears on the selection screen).
More information related to the Constant Selection feature can be found on our WIKI page:
OT-OLAP-CS - SAP NetWeaver Business Warehouse - SCN Wiki