Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jdormer
Helper I
Helper I

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
Community Champion
Community Champion

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

 

 

 

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
Community Champion
Community Champion

How are these 3 table releated?

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.