Dynamics 365 F&O/AX Dynamics 365 CE / CRM Dynamics GP Business Central / NAV UG Dynamics SL Power Platform Fabric UG MS Copilot UG

[Meredith Pritchett]

My calculation to obtain the ROI is not populating correctly. Sorry, I’ve been using MR for a couple of years but new to adding formulas (and first time posting to this forum, so bear with me). 🙂

This is the line in question:

1158

ROI (Net/Expenses)

CAL

@1090/@1000

###.##%;(###.##%)

CS

Initially, I didn’t have the “###.##%;(###.##%)” and I was receiving a “2” as my ROI, but after seeing the formula in a prior post, I added it, and now I get “200%”. I’m close but the ROI should be 176.1%.

Any suggestions?

Thanks,
Meredith

——————————
Meredith Pritchett
Accounting Manager
International Society for Pharmaceutical Engineering
Tampa FL
——————————

[Steve Erbach]

Meredith,

For what it’s worth, the Format Override I use for percentages is:

###0.0%;(###0.0%);

That gives accuracy to a tenth of a percentage point.

Sincerely,

——————————
“Sparkly” Steve Erbach – Business Analyst & MS Dynamics Platform Administrator
WOW Logistics Company – Appleton, WI
VP for Communication, GPUG WI (Milwaukee) Chapter
Co-Chair, GPUG WI (Green Bay) Chapter
GP 2016 (16.00.0439) / MR 2012 CU15
Blog: https://www.gpug.com/blogs/steve-erbach
Twitter: @serbach
——————————
Welcome to Dynamics!
You do not have permission.
Call Steve. (hat tip: Lou Spevack)
——————————
——————————————-

[Meredith Pritchett]

Thanks Steve.  I do have the formula in the Format Override column and changed it to what you indicated but I still get 200.0%.  I must be missing something but not sure what that is.

——————————
Meredith Pritchett
Accounting Manager
International Society for Pharmaceutical Engineering
Tampa FL
——————————
——————————————-

[Meredith Pritchett]

Could it have anything to do with the “Format / Currency Override” line on the Column Definition?  It has #,###,##0;(#,###,##0) but the ROI is the only field in the column that needs a %, everything else is $.

——————————
Meredith Pritchett
Accounting Manager
International Society for Pharmaceutical Engineering
Tampa FL
——————————
——————————————-

[Steve Erbach]

Meredith,

It definitely could! You might try experimenting with the Rounding precision on the Report Definition’s Settings tab. Since your Column Definition Format / Currency Override displays whole numbers with parentheses around the negatives, I think you could get rid of that override format and change the Report Definition Rounding precision to “Whole Dollars” and the “Display negative numbers with:” setting to “()”. Then having a format override in your Row Definition should work out.

Regards,

——————————
“Sparkly” Steve Erbach – Business Analyst & MS Dynamics Platform Administrator
WOW Logistics Company – Appleton, WI
VP for Communication, GPUG WI (Milwaukee) Chapter
Co-Chair, GPUG WI (Green Bay) Chapter
GP 2016 (16.00.0439) / MR 2012 CU15
Blog: https://www.gpug.com/blogs/steve-erbach
Twitter: @serbach
——————————
Welcome to Dynamics!
You do not have permission.
Call Steve. (hat tip: Lou Spevack)
——————————
——————————————-

[Derek Krebs]

My added two cents for testing/fix/options:

• Report Definition: no rounding
• Column: no format overrides for numbers, leave blank
• Row: apply number format override with percentage and 2 or more decimals and even multiple by 100 in case formula typo or small amount for general testing tip for other scenarios, do not use the CS (Currency symbol) or other print control; reminder that any row formatting overrides column formatting

——————————
Derek Krebs
Sr Consultant
MSX Group
derek.krebs@msxgroup.com
——————————
——————————————-

[Steve Erbach]

Derek,

Good point about the Currency Symbol. Unnececessary for a percentage!

Sincerely,

——————————
“Sparkly” Steve Erbach – Business Analyst & MS Dynamics Platform Administrator
WOW Logistics Company – Appleton, WI
VP for Communication, GPUG WI (Milwaukee) Chapter
Co-Chair, GPUG WI (Green Bay) Chapter
GP 2016 (16.00.0439) / MR 2012 CU15
Blog: https://www.gpug.com/blogs/steve-erbach
Twitter: @serbach
——————————
Welcome to Dynamics!
You do not have permission.
Call Steve. (hat tip: Lou Spevack)
——————————
——————————————-

[Jen Kuntz]

Do you have rounding turned on this report? That’s what it sounds like. On any given row you can suppress rounding to get around things like this. If I recall correctly it’s “SR” in the same column where you have the CS code on your screenshot. 

——————————
Jen Kuntz, CPA, CGA
Microsoft MVP, Business Solutions
Kuntz Consulting Inc.
Cambridge, ON, Canada
——————————
——————————————-

[Brad Johnson]

Might also check your report definition to try the other “Calculation Priority” on your Settings tab (Col then Row vs. Row then Col.)  depending on your other calcs, that may change things.

——————————
Brad Johnson
Financial Analyst
Samtec Inc.
New Albany IN
——————————
——————————————-

[Meredith Pritchett]

Thank you all for your suggestions.  After doing some tweaking with the different options suggested, I was able to get the report to work.  I left the Rounding precision to Whole Dollars in Report Definition. In Column, I left blank the Format/Currency Override. In Row, I removed the “CS”.  🙂

Thanks again,
Meredith

——————————
Meredith Pritchett
Accounting Manager
International Society for Pharmaceutical Engineering
Tampa FL
——————————
——————————————-