Skip to main content
Solved

Advanced Formula Help for Report

  • 17 April 2024
  • 3 replies
  • 57 views

Hey AW Community, 

 

I’m in flight of creating a profitability report for senior management and finance and I’m in a bit of a conundrum as they want to see slightly different things from the same field within our system. My only solution to this is to create a formula for one of the report tabs to give them what they need.

 

SCENARIO: 

We have our actual Revenue column pulling through billable effort for an engagement(as expected). As with any projects, we will have Change requests; and based on the additional project codes for these changes, we can see whether this burn is billable to the client or non billable.

 

For the Finance tab, where the effort is not Billable - we want to show the actual revenue as ‘0’. (The current functionality of the ‘actual revenue’ field needs to remain in tact for the other report tabs.

 

Question: 

What type of formula do I need to write in order for my ‘actual Revenue’ on these selective non billable endeavours to show as ‘0’ for that particular tab in my report. 

 

I’m trying to achieve something along the lines of IF Actual revenue for project codes beginning with ANB is more than 0, then make it 0.

 

Help please?! 

Hi Hayley

In general, any work item in the Project Work Plan will show Actual Revenue for work items that are marked billable, and will not show Actual Revenue for work items that are not marked as billable.

I’m missing information on:

  • What are you running your report on, on Projects or Work Items or on Tasks?
  • How is your report structured?
  • How do you reference Change Requests? Are those represented as Tasks within a project?

 

Hi Limori, 

 

Thank you for responding. 

So I understand that items marked as billable will show Actual Revenue. This is the expected behaviour and how we need it to stay for our other reports. However for context - we may get senior mgmt approval to do work that we charge back to the business in order to protect our profitability margins within our department. However this is not money that finance can recoup as it is all internal - hence why for this report alone we would want these particular projects with project type ‘Approved Non Billable’ or prefix ‘ANB’ - for their actuals to be 0.  

  1. We are running the report on Projects (We have project types and prefixed project codes to help us distinguish & categorise)  
  2. The report looks at Portfolio > Projects. 
  3. In this instance, these type of Change requests get a new project within the system and are categorised with a different prefix ‘ANB’

I’ve added an extract of one of the scenarios. The figures in the red box correlate to 1 engagement, however the figure highlighted in Yellow is the figure that needs to show ‘0’ on this report tab only

 

I hope that hasn’t confused the matter even further 😅

 

Not sure why this is marked as Solved….

Reply


Terms of UseCookie settings