Finance | Project Operations, Human Resources, ...

MR total calculation

(0) Share

Report

Posted on by Nataliia Samoilenko

Hi all,

Could you help me with this problem.

I have issue with total calculation column. The set up of rows and columns as follows:

if I set up "Perform column calculation first and then row" in report settings, it is calculated correctly in total column for percentage row (greenlighted), but I have incorrect amounts in totals column for row (yellowlighted) where i have such formula 'C=@520,D=@520+@550,E=@520,F=@520.

I set up "Perform row calculation first and then column" in report settings. It is calculated incorrectly for in total column for percentage row (yellowlighted). but I have correct amounts in total column for row (greenlighted) where i have such formula.'C=@520,D=@520+@550,E=@520,F=@520.

Are there any ideas how to overcome this issue?

All responses (5)

Answers (0)

Suggested answer

Rasha Wanees on at

Like (0)

Report

RE: MR total calculation

Hi Natalia,

How about if you left set up "Perform row calculation first and then column" in report settings. and modify F calculation to be F= C.520+D.520+E.520.
Nataliia Samoilenko 7 on at

Like (0)

Report

RE: MR total calculation

Hi Ludwig,

Thank for your answer.I try to use CBR, it did not help.May be some other ideas?

I think that my last calculations in the updated below example are consistent.

So my situation is the following:

it is different rule of cogs calculation for different legal entities.

I have side by side PL report with these legal entites in columns, and total column.

i have such formula for total cogs row :C=@520,D=@520+@550,E=@520,F=@520.

this formula works correctly for the total cogs row for total column ,

if I set up "Perform column calculation first and then row" . It is calculated total cogs for all legal entities as 60+70+70-30=170

But the margin row for total column is calculated incorrectly: as total amount of other percentage rows 40%+66%+50%=156.67%

if I set up "Perform row calculation first and then column" it calculates correctly margin row for total column as 160/360*100=44%

But total cogs row for total column calculated incorrectly as 60+70+70=200

The updated report set up (it was incorrrect total formula AU+AV+AW in this example. But in real report it was correct):
Suggested answer

Ludwig Reinhard Microsoft Employee on at

Like (1)

Report

RE: MR total calculation

Hi,

The numbers are still not fully correct but have you tried making use of the CBR functionality and some non-printed lines and columns?

Here is an example that illustrates this functionality

www.youtube.com/watch

Best regards,

Ludwig
Nataliia Samoilenko 7 on at

Like (0)

Report

RE: MR total calculation

Hi , Ludwig,

thank you for answer.

I double checked my calcuations. there was incorrect sign for correction.

but For le1 Total COGS should be equal to COGS. So it should be 60.

Because i have specfic formula for total cogs.

LE1 is in column C. So C=@520.

C=@520,D=@520+@550,E=@520,F=@520
Suggested answer

Ludwig Reinhard Microsoft Employee on at

Like (1)

Report

RE: MR total calculation

Hello Nataliia,

Can you double check the calculations made in the excel sheets that you shared.

If I do similar calculations I get different results