Skip to Content
Jun 10, 2013 at 02:47 PM

SUMCT function does not use sum of values in column


Good morning.

I have a BEx report that has a formula in it that derives weighted value based on values in other columns. If 1 or more of the other columns has a blank value, the weighting factor will be different. My problem is when it comes to the Result row. By default, BEx is repeating that same weighted calculation across the values of the Results row which is not returning the same value of the sum of the weighted factor column. I can set the Result value of "Calculate Result As" to Summation which returns the expected amount for that column total which works great for that column. However, when I use the SUMCT function in another calculation (to derive a percent value based on the weighted value) BEx is using the default behavior to determine the total which is giving me an incorrect percentage value. Is there any way to get BEx to use the Sum of the column values in the other formula rather than calculating the value of the Result row?

Here is an example:

Qty1 Qty2 Weighted Value (Summation in Calculate Result As) Weighted Value (Default BEx behavior) Percentage value
100 60 2.17 2.17 39.65%
200 0.33 0.33 6.10%

240 0.00 0.00
RESULT 540 60 2.50 5.47 100%

Any help and insight would be greatly appreciated!