Query Designer: Calculate a % based on two other totals

[Former Member]

Hi Gurus,

Here is the scenario of my query:

A B C D E

2 3 6 4 150%

1 2 2 4 50%

2 2 4 8 50%

1 1 1 10 10%

-

---

6 8 13 26 50%

Column C is calulated by AB and I am able to get the total 13 instead of 48 (6😎 by using the "Total" calculation function.

The problem is about the Total of Column E. It is a % based on columns C versus D. There's no problem for individual results, but for the total I cannot get the 50% because my query calculates the total result comparing 48 (6*8) Vs 26.

If I also use the Total function for the E Column, what it shows is an addition of all the individual results, so 260%.

I can't find the way to get the Total 50% that takes the "Total" function of Column C and Column D.

Please, any idea so solve this scenario?

Many thanks!!

Regards,

Jordi

[Former Member · Accepted solution]

Hi,

The "Calculate Result As" is a Display option. That value is not considered in the calculations, only for display.

You may try "Exception Aggregation" , 'Total' for Column C with Ref Characteristic being the Char at the lowest level of which you see the data and try.

For eg: you can to calculate A x B for Materials, but at all levels higher than material you want A1 x B1 + A2 x B2 + .....

Then you create CKF/Formula C = A x B with Exp Aggregation, Total, Ref Char = Material.

[Former Member]

Hope you are using Formula for Column C, instead Use Calculated Key Figure and try.