BEx Query Calculated KF

[laxmikantdornal]

D

ear Team,

We are using BPC cube for reporting.

We have single keyfigure as it is bpc cube, we are using KEYFIGURE DIMENSION to identify the kyfigures (AMT, QTY, NORM, PRICE etc.)

Now I have data at Account level.

The requirement is to calculate the PRICE from AMT and QTY as the BPC stores only AMT and QTY and NORMS and PRICE are calculated locally in excel.

Now the PRICE and NORMS are CALCULATED based on ACCOUNT.

Account is in ROW column

I have created the two key-figures (restricted with account)

1. Production QTY restricted withProduction account

2.Consumption QTY restricted with Consumption accounts (Like, raw material, chemicals, utilities etc.)

Then I have created one calculated key figure of NORMS

Formula used is

NORMS=Consumption QTY/ Produciton QTY

Now problem is calculations are happening row wise and hence in the rawmaterial row there is no Production and hence norms is not calculated or in production row there is no consumption and norms are not calculated

Desired results required is as below

Account Production
Quantity-QTY
MT Consumption
Quanity-QTY
MT AMT
Rs NORMS
MT/MT PRICE
Rs/MT Production 100 Raw Material 110 2200 1.1 22 Chemical 10 500 0.1 5

Query giving result as below

Account Production
Quantity-QTY
MT Consumption
Quanity-QTY
MT AMT
Rs NORMS
MT/MT PRICE
Rs/MT Production 100 Raw Material 110 2200 Chemical 10 500

The result of query is as below

Account NORMS Consumption QTY Production QTY Production 65,100.60 By Product -12,41,07,988.75 Raw Material 2,97,09,77,593.62 Catalyst & Chemicals 11,14,39,044.34 Packaging Cost 2,38,21,215.70 Utilities - Plant 9,77,91,247.05 Stores & Spares- Other than Shutdown 1,63,40,250.60 Overall Result 49,216.58 3,09,62,61,362.56 65,100.60

I want the 65,100.60 should be a constant devider in all the accounts in NORMS caclculation.

I have tried with having different different combination but i was unable to make it.

Pls suggest how to proceed.