Dynamics GP
Dynamics GP
Organizer:
- Organized by
-
-
MR – switching from row calc to column calc for a specific calculation
-
MR – switching from row calc to column calc for a specific calculation
-
I’ve created a budget report that has the last four quarters of actuals, a TTM Actuals column, a NTM Budget column and the next four quarters of budget. I’m trying to figure out a way for my TTM and NTM Gross Margin % to calculate based on the column data (ie $4.032M / $15.837M) as opposed to summing up the four quarters (ie 28% + 23% + 25% + 24%) like the rest of the report above and below. Is it possible to change the calc methodology from one or more particular “cells” in the report? If I switch the calc priority to Columns then Rows, my variance totals get added up, and I end up with 400% or 900% variance.I looked through a few prior post (hyper links below) and could not find a solution. Or maybe I just was not using the right search keywords. Thoughts on how others have navigated around this scenario?
——————————
Chris Pang
Financial Planning and Analysis Manager
GEE Group, Inc.
Jacksonville FL
—————————— -
Hey Chris,
When you have multiple calculations in both the column and row definitions in the report, it can sometimes be difficult to get exactly the results you are looking for. As you seem to have discovered, you can change the calc order and it may fix the calculation in question, but it may break another. There is a possible solution, but it’s not usually simple and it usually involves some trial and error. You can create column-specific row calculations by identifying the row/column intersection (element) in the formula. For example, the formula might be something like F=@1000/F.500 where F is the TTM column, 1000 is the Gross Profit row code and 500 is the Total Revenue. This kind of formula, used in conjunction with hidden columns and rows can give you the result you are looking for. As I said, it’s not usually simple. Basically, you need to get the formula to show the correct value somewhere in the report (even if it is in a hidden column) and use a formula like the one shown above to display it where you need it.
Good luck. If you need further assistance, please feel free to reach out to me directly.
Regards,
Nick Sercer
nick.sercer@msxgroup.com——————————
Nick Sercer, Partner/Consultant at MSX Group
nick.sercer@msxgroup.comView GPUG Partner listing for MSX Group: https://www.gpug.com/engage/findacompany/companyprofile?UserKey=275d1f3a-73b4-4a58-b0d8-7395d682dbc3
http://www.msxgroup.com/ProsperoGP
——————————
——————————————-
-
-
0 Replies
Sorry, there were no replies found.
The discussion ‘MR – switching from row calc to column calc for a specific calculation’ is closed to new replies.
