Query Designer: Calculate a % based on two other totals
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 (68) 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?
Parth Kulkarni replied
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.