We have a key figure which is defined as "Number: DEC - Counter or Amount field with comma and sign" in RSA1. Aggregation is set as Sum and exception aggregation is set as summation as well. We use this key figure to store % of rebate for each transactional record in the cube. The report has profit center hierarchy and this key figure is showing up cumulative in the results display, which is erraneous.
For e.g., A country has 3 regions and each region has 3 profit centers. So P1 to P3 belong to Region 1 etc.. The trasactional data is
In the report we see
Obviously the above results are wrong. Do I have to do the "net sales" calculations in the cube level itself?? Is there any other way? Instead of summation I could use "average" in the exception aggregation, but the average rebate is not accurate either. How do we change the settings of the rebate key figure so that we get accurate results at each level of the hierarchy? The settings should enable me to calculate net sales record by record and then cumulate. I couldn't access the metadata repository as it was disabled in our system to look up any existing % rebate key figures. Can somebody please give some pointers in this regard? I really appreciate your help.