Skip to Content
avatar image
Former Member

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 1 Customer 2 Customer 3 PType1 10 15 20 PType2 15 1 11 PType3 3 3 10 Results 18 19 41

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:

Customer1 KFTotal Customer2 KFTotal Customer3 KFTotal PType1 10 18 15 19 20 41 PType2 15 18 1 19 11 41 PType3 3 18 3 19 10 41 Results 18 19 41

       

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

7 Answers

  • Best Answer
    Jan 14, 2015 at 05:19 AM

    Dear John,

    Using formula variables it will be tricky.

    I tried with SUMCT and it is working fine.  Please see screenshots below.

    1. Create Formula with data function SUMCT

    2. Go to calculation tab of formula and hide "Calculate Results as"

    3.For vendor make it Results row "Always Dsiplay"

    4. See the below result

    Thanks


    total1.PNG (2.0 kB)
    Total5.png (9.9 kB)
    Total3.PNG (1.6 kB)
    Total4.PNG (3.8 kB)
    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jan 13, 2015 at 07:21 PM

    did you try cells?

    Add comment
    10|10000 characters needed characters exceeded

  • Jan 13, 2015 at 07:48 PM

    Hi John,

    1 Create replacement path formula variable for customer 1 ,2 and 3 and keep all in COLUMNS

    2 Place TYPE(PType1,2,3) characteristic in Rows and result always display

    3 Create three formulas for customer 1 ,2 and 3 to display overall result like 18,19, 41 by using  SUMGT function to display side of each customer

    Regards,

    Nanda

    Add comment
    10|10000 characters needed characters exceeded

  • Jan 14, 2015 at 05:25 AM

    Hi John,

    Without Exception..Cant we use it this way,

    CKF1= Customer 1 values as you have shown...

    CKF2= SUMGT/SUMCT (CKF1)

    rgds

    SVU

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jan 14, 2015 at 07:00 AM

    Hi John,

    As kool Sudha explained, it should work.. as you said you are getting 'x'

    Can you elaborate more so we can identify the gap(if possible with screen shots).

    Regards,

    Santu

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jan 14, 2015 at 08:00 AM

    Hi John,

    Have got the solution for your requirement,if yes then please share with us so that it may helpful us .

    Thanks,

    sunil

    Add comment
    10|10000 characters needed characters exceeded

  • Jan 14, 2015 at 11:39 AM

    Hi John,

    I am predicting that you have some cells for key figures as "*". may be thats why you are getting "X".

    Try to remove those and you will get the correct result.  Try with oss note 1013127 as well

    Thanks

    Add comment
    10|10000 characters needed characters exceeded