Wrong Totals at Drilldown

[Former Member]

Hello Experts,

This is my problem: when I run a query at maximum level (Customer, Invoice, Item) it works fine (calculations and the whole totals)

Customer / Invoice / Item / Units / Price / Total

33 / 9003 / 388 / 60 / 150 / 9,000 <- detailed total is well calculated 60*150=9000

33 / 9003 / 23272 / 24 / 150 / 3,600

33 / 9003 / 30767 / 36 / 150 / 5,400

-

---

/ 120 / / 18,000 <- Total Invoice 9003 (is summarized correctly)

33 / 9004 / 930 / 144 / 130 / 18,720

33 / 9004 / 4685 / 14 / 99 / 1,386

33 / 9004 / 4696 / 6 / 127 / 762

-

---

/ 164 / / 20,868 <- Total Invoice 9003 (is summarized correctly)

/ 284 / / 38,868 <- Total Customer 33 (is summarized correctly)

But when I remove drilldowns to see totals by Invoice or Customer, figures are wrong:

Customer / Invoice / Units / Price / Total

33 / 9003 / 120 / / 54,000 <- WRONG, should be 18,000, what the query does is 120*450(price summation)=54,000

33 / 9004 / 164 / / 58,384 <- WRONG, should be 20,868

Customer / Units / Price / Total

33 / 284 / / 228,906 <- WRONG should be 38,868

Price is: Calculate Result As… Suppress Result

Total is: Calculate Result As… Summation

How can I fix this total calculation problem? We need to see the query at any level. Our BW version is 3.5 Thanks for your help in advance.

[Former Member · Accepted solution]

Hi,

Guess, your price is a Key figure (not characteristic or attribute).

If so, goto RSD1 and check the 'exception aggregation' in the 'aggregation' tab. It could be 'Summation' change it to 'Average' or 'Min' or 'Max', as per your requirement.

The case is that if price was different, how the price should be considered, Avg, first value, or last value or how?

This is the setting causing the behaviour, you mentioned.

HTH,

Regards,

Nataraj.

[Former Member · Accepted solution]

Thanks for your response and you're right, is a Key Figure. But for this case every item could have a different price, I can't use a minimum, maximum, average, etc. I should take its own price. Whatever, I tried the report modifing the price's aggregation to "No aggregation", but it didn't work.

As I explained before, the problem is only showed when I see the query summarized by Invoice, Customer or Final Totals (without Item detail).

Regards

[Former Member · Accepted solution]

Hi,

I assume total is a calculated key figure. Since your calculated key figure,total, contains product of

two values, when you go to the property window of that calculated key figure in the selection window, there is an option "Aggregation which contains time of calculation that has two options before aggregation and after aggregation", available at the end of the window. choosing

before aggregation results in calculation for each record in cube and hence should display correct value in

your case.

for more info look at the following link :

http://help.sap.com/saphelp_bw31/helpdata/en/6f/56853c08c7aa11e10000000a11405a/frameset.htm

let me know the result.

thanks,

Madhav

Message was edited by: Madhav Ghantasala

[Former Member · Accepted solution]

Thanks for your response. Yes, Total is a calculated KF and I modified Total KF but a new message is showed: "Before Aggregation" is not permited at calculation time. So I couldn't even test it. Any other ideas?

[Former Member · Accepted solution]

Didn’t find the solution to this problem. What I did is to make all calculations at Transfer Rules time, creating all the new result objects I needed.

In BEx just printed these new objects as a regular KF using regular summation. It worked.

Hope this help someone.