Skip to Content
0
Former Member
Apr 04, 2014 at 03:24 AM

SUMCT & SUMGT & SUMRT not working properly

1155 Views

Hello Gurus,

I am trying to divide the Totals at region level with Profit Center Level. For


Produced MH % = Profit center Volume / Region Total Volume

Region

Department

Profit Center

Jan

Feb

Mar

Apr

MXN1 CHRYSLER P105010 Volume per Month Total GUM 450 450 450 450 MXN1 CHRYSLER P105010 Produced MH % 0.30 0.30 0.30 0.30 MXN1 CHRYSLER P105020 Volume per Month Total GUM 1050 1050 1050 1050 MXN1 CHRYSLER P105020 Produced MH % 0.70 0.70 0.70 0.70 Total at Region Level 1,500.00 1,500.00 1,500.00 1,500.00 MXNW COMBU P105022 Volume per Month Total GUM 800 800 800 800 MXNW COMBU P105022 Produced MH % 0.62 0.67 0.62 0.67 MXNW CVG P105035 Volume per Month Total GUM 500 400 500 400 MXNW CVG P105035 Produced MH % 0.38 0.33 0.38 0.33 Total at Region Level 1,300.00 1,200.00 1,300.00 1,200.00

For example - Produced MH % = 450 / ( 450 + 1050 ) = 450 / 1500 = 0.30 for Jan Month.

I am applying formula = Volume Per month Total GUM / SUMGT ( Volume Per Month Total GUM )

Above formula is working correct if User Selects One Region MXN1, But If user selects More than One Region, SUMGT is add all regions and dividing based on the formula....

I tried Aggregation by Region and Calculation, but no luck.

Please any once can share how to resolve if user enters more than one region in the selection.

Thanks

Ganesh Reddy.