cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jdormer Regular Visitor
Regular Visitor

Measure evaluates data at engagement level. How can I SUM these values to get company-wide value?

Looking for some assistance with a "Work Remaining" report I have built. 

 

Currently the report is displaying four values:

 

POAmount - Existing column in original dataset

AmountInvoiced - Existing column in original dataset

A measure called RevSinceInvoice = CALCULATE(
SUM('Time'[Revenue]),
FILTER('Time', 'Time'[Date]> (MAX(Invoices[Created])
)))

A measure called PORemaining = SUM(Engagement[POAmount]) - [RevSinceInvoice] - SUM(Invoices[Amount])

 

I have slicers on the report so I can view by client and engagement the original PO Amount, the amount that has been invoiced, work that has been performed since the last invoice (RevSinceInvoice), and the amount of work remaining to be performed (PORemaining). This all works as expected when viewing a single engagement. 

 

However, there is also a need to view the same information at a company level, for all clients and all engagements. When I do not make a selection in the slicers, attempting to view company wide data, the RevSinceInvoice measure is picking up a single date for MAX(Invoices[Created]), which means that only 'Time'[Revenue] entries after the most recent invoice for any client are reflected in RevSinceInvoice. What I really need is a MAX(Invoices[Created] for each client so that all 'Time'[Revenue] entries are evaluated against that client's most recent invoice, not the most recent of all invoices. 

 

1) I hope that makes sense. 

2) How can I aggregate the RevSinceInvoice values to get the correct company-wide figure?

 

Thanks.

3 REPLIES 3
Sean Super Contributor
Super Contributor

Re: Measure evaluates data at engagement level. How can I SUM these values to get company-wide value

what are the tables and columns involved in this Calculation?

 

Time Table

Revenue??? (why is Revenue in Time - I guess that's a measure that just ended up there)

 

Engagement Table

POAmount,

 

Invoices Table

Amount, Created

 

 

 

jdormer Regular Visitor
Regular Visitor

Re: Measure evaluates data at engagement level. How can I SUM these values to get company-wide value

Hi @Sean

 

I'm pulling information from the database that is behind the application we use for time tracking. Revenue is a column in the Time table that is calculated based on the number of hours billed x rate, showing the revenue earned for that time entry. 

 

EngagementSnip.PNGInvoiceSnip.PNGTimeSnip.PNG

 

There are other tables as well that I am not directly interacting with in my report:

-Billing

-Client

-Deliverable

-Fees

-Managers

-Users

-Worktypes

 

Does that help clarify? Let me know if there's other information I can provide that would be helpful. 

 

Thanks.

 

 

Sean Super Contributor
Super Contributor

Re: Measure evaluates data at engagement level. How can I SUM these values to get company-wide value

How are these 3 table releated?

 

 

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 396 members 4,196 guests
Please welcome our newest community members: