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?

I have the same question (0)

All responses (5)

Answers (0)

Sort by

Suggested answer

Ludwig Reinhard Microsoft Employee on at

Like (1)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Nataliia Samoilenko 7 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Suggested answer

Ludwig Reinhard Microsoft Employee on at

Like (1)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Nataliia Samoilenko 7 on at

Like (0)

Report
Copy link

Link copied!

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):

Was this reply helpful? Yes No
Suggested answer

Rasha Wanees Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No