AUGForums.com

An Acumatica User Group

  • Free
    • Start Here
    • Rolodex
    • Podcast
    • Blog
    • Forums
  • Course
  • Consulting
  • Login
  • Register
Acumatica Forums

By using this website, you agree to our Terms of Use (click here)

  • Overview
  • Member List
  • All-Stars
  • Stats
  • Recent Posts
  • Help
Forums
AUG Forums
Acumatica Analytica...
Percentage of Total...
 
Notifications
Clear all

In order to allow me to focus on Consulting (click here) and Courses (click here), I continue to review all posts here in the Forums, but, in general, I'm only personally responding to posts that I mark as #AcumaticaTnT (click here). For Questions (click here), others may respond, or you can post over at Community.Acumatica.com (click here).
Sincerely,
Tim Rodman

#AcumaticaTnT Percentage of Total Revenue and Percentage of Total Expenses

Acumatica Analytical Report Manager (Financial Report Writer)
Last Post by Foujan Khazaee 3 weeks ago
12 Posts
6 Users
4 Likes
4,388 Views
RSS
Tim Rodman
Posts: 2969
 Tim Rodman
Admin
Topic starter
April 11, 2018 11:04 pm
(@timrodman)
Member
Joined: 8 years ago

If you have a Profit & Loss and you want to add a column that shows each line as a percentage of the total, it gets a little tricky if you want to change the denominator of the calculation. For the revenue lines, you might want to show a percentage of Total Revenue. For the expense lines, you might want to show the percentage of Total Expenses. So the denominator changes as you move down the report.

Let's say you have a report that looks like this:

 

And you want to add another column showing each line in the Revenue section as a percentage of Total Revenue, then each line in the Cost of Revenue section as a percentage of Total Cost of Revenue.

First, you need to go into the Row Set and populate the Base Row column for each line which is how you tell each line what the denominator will be in the percentage of calculation.

 

Next, you need to go into the Column Set and add a new calculated column with the following formula:

=C/Value(@BaseRowCode,'C')

Note that @BaseRowCode reads from the Base Row column in the Row Set.

Also note that P in the Format row is the shortcode for formatting the result as a percentage.

 

In the screenshot above, if the denominator wasn't changing throughout the report, you could hardcode the row number instead of using @BaseRowCode. Let's say you wanted every single line to show the percentage of Total Revenue, you could use this simpler formula:

=C/C0200

 

But since we want a changing denominator, we have to use the more complicated formula above which uses @BaseRowCode.

Using the following formula, we get the following results:

=C/Value(@BaseRowCode,'C')

11 Replies
Shawn P Slavin
Posts: 196
 Shawn P Slavin
April 11, 2018 11:46 pm
(@shawn-p-slavin)
Member
Joined: 3 years ago

For those familiar with an now discontinued product called FRx, this is a familiar feature.  It was designed explicitly for the application you outline above. The trick is to ensure to define and/or change the base row BEFORE you need to use in.

Nice write up.

If interested, folks can find additional documentation and examples in the F350 training guild starting on page 35.

F350AnalyticalReports2017R2.pdf

Reply
Tim Rodman reacted
Bodonna
Posts: 27
 Bodonna
November 21, 2018 5:41 pm
(@bodonna)
Member
Joined: 5 years ago

Is it possible to do this for each column?  When I tried it only calculates the first % ?

Reply
Tim Rodman
Posts: 2969
 Tim Rodman
Admin
Topic starter
November 27, 2018 10:17 pm
(@timrodman)
Member
Joined: 8 years ago

The formula above references Column C, but you could easily add an additional calculated column that references Column B.

Reply
Bodonna
Posts: 27
 Bodonna
November 28, 2018 1:53 pm
(@bodonna)
Member
Joined: 5 years ago

When I tried that it only calculates in one of the columns. 🙁 

Reply
Bodonna
 Bodonna
(@bodonna)
Joined: 5 years ago

Member
Posts: 27
December 6, 2018 10:33 am
Reply toBodonnaBodonna

These are the results I get.

Reply
Tim Rodman
Posts: 2969
 Tim Rodman
Admin
Topic starter
December 8, 2018 8:56 am
(@timrodman)
Member
Joined: 8 years ago

Are you referring to why your Column O is empty? Try changing the Type from GL to Calc.

Reply
Bodonna
 Bodonna
(@bodonna)
Joined: 5 years ago

Member
Posts: 27
December 10, 2018 11:25 am
Reply toTim RodmanTim Rodman

That was it!!!!  Thanks!!!!

Reply
Tim Rodman reacted
kalumw
Posts: 7
 kalumw
March 15, 2021 8:40 pm
(@kalumw)
Member
Joined: 2 years ago

Hey Tim, 

Thanks for the article, it's been really useful. However, I've been having issues when trying to show a total line as a percentage of another line. It just returns 100%. 

For example I'd like to see my COGS as a percentage of my total income, so I'm totalling my COGS and using the Base row of my total income. 

image
image

 

As you can (hopefully) see in the screenshot, my percentage is coming back at 100%.

Reply
Tim Rodman
Posts: 2969
 Tim Rodman
Admin
Topic starter
May 14, 2021 2:00 am
(@timrodman)
Member
Joined: 8 years ago

What does your Column Set look like?

Reply
Zsolomon
Posts: 6
 Zsolomon
November 22, 2021 11:58 am
(@zsolomon)
Member
Joined: 2 years ago

Hello, 

Is it possible to add the percentages to just rows that show Totals? I have not been successful using IIF statements referencing @BASEROWCODE. That formula only seemed to work when I filled in @BASEROWCODE for rows that did not include totals. 

Reply
Foujan Khazaee and Tim Rodman reacted
Foujan Khazaee
Posts: 1
 Foujan Khazaee
March 1, 2023 10:37 pm
(@foujan-khazaee)
Member
Joined: 1 year ago

Thank Tim.
I managed to add percentage for all columns. The only rows don't show the % is for Totals.

For instance, I need to show % for Gross Profit out of Total Income, but still showing nothing.

P and L
Reply
Forum Jump:
  Previous Topic
Next Topic  
Forum Information
Recent Posts
Unread Posts
Tags
  • 11 Forums
  • 2,268 Topics
  • 9,982 Posts
  • 0 Online
  • 1,834 Members
Our newest member: Derek Wong
Latest Post: Change the number of rows in the check remittance field
Forum Icons: Forum contains no unread posts Forum contains unread posts
Topic Icons: Not Replied Replied Active Hot Sticky Unapproved Solved Private Closed

Online Members

 No online members at the moment

Acumatica Forums

Terms of Use & Disclaimers :: Privacy Policy

Copyright © 2023 · AUG Forums, LLC. All rights reserved. This website is not owned, affiliated with, or endorsed by Acumatica, Inc.

‹›×

    ‹›×