Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

SAP BI/BW - Calculate the total on individual Columns on BEX

Hello

I have a situation using BEX query.

The case is as follows, I have several customers for different type of products.

Customer 1Customer 2Customer 3
PType1101520
PType215111
PType33310
Results181941

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:

Customer1KFTotalCustomer2KFTotalCustomer3KFTotal
PType1101815192041
PType215181191141
PType33183191041
Results181941

       

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:

  1. 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.

  1. 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.

  1. Use Data function SUMRT on the values for each customer. Same issue as with SUMGT.
  2. 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.

Thanks

Tags:
Former Member
replied

Hi John,


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).

Kind of:

More information related to the Constant Selection feature can be found on our WIKI page:
OT-OLAP-CS - SAP NetWeaver Business Warehouse - SCN Wiki

1 View this answer in context
Not what you were looking for? View more on this topic or Ask a question