Divide one KFu00B4s line value with another KFu00B4s total value?

[Former Member]

I have a problem in my query; I need to divide one key figures line value with another key figures report total value, see example below. For instance I need to divide Material A’s price with the total invoice amount for all materials.

”Material” “Price/Unit” ”InvoiceAmount” “Weighed index”

A..............10..................350.............(10/1000)=0,01

B..............20..................350..............(20/1000)=0,02

C..............30..................300..............(30/1000)=0,03

-

---

..................................1000

Any suggestions on how to solve this problem? I’m currently using formulas.

Best Regards

/Daniel

[former_member188975 · Accepted solution]

You can try the following:

1. Create a new local formula F1 = SUMGT (InvoiceAmount)

2. Create Weighed index = Price / F1

3. You can set F1 properties to Hide

[Former Member · Accepted solution]

Thanks,

is that dependant on the values I see on the screen?

Is there a way to get the total sum from all rows in the cube into a Calculated Key Figure or such?

[former_member188975 · Accepted solution]

Yes, SUMGT changes with the navigation...if your report pulls up 5 materials and the total InvAmt is 1000, and then you filter to 3 materials, the SUMGT will change.

You can use SUMRT which does not change with navigation...the total value in this case will remain to what is calculated when the report is excuted.

These functions are not available on the left hand side of the query designer, i.e. if you want to make a CKF.

[Former Member · Accepted solution]

Thanks my friend - that really helped me!

Thanks to Tom too!

Best Regards

/D

[former_member188975 · Accepted solution]

Hey..you're welcome!

[Former Member · Accepted solution]

Hello,

Hey guys that was exellent, Its new to me. Thanks

Where can I see all the functions available.

How can we use a "If" condition in a formula

my request is slightly different.

A B C D= C*B

Mat Comp type Qty1 qty2 ratio needed

20001 10001 C 10 18 10/15 ?

20001 10002 C 20 40 20/15 ?

20001 20001 M 15 30 15/15 ?

How to do column C?

you guys showed how to use SUMGT() and SUMRT()

Here one material can have several components , In which one of its component would be material itself. The field "type" describes that, so while calulating the field "C" I need to take the value QTy1 for the component and divide it by the qty1 of the material

basically I wanted to see the percentage of components in a material.

hope this is clear with you

thanks for your kind help.

its slightly complicated na.....

regards

Meps

[tom_francis2]

You can indeed do this using formulaes. There is one special formulae to retreive a total for any column and use this. I don't have a system available for the moment. But it should work.

Any other problem?

Tom

[Former Member]

'