Announcements
cancel
Showing results for
Did you mean:
Not applicable

## Calculating Project Cost

Hi All,

I am trying to make a operational dashboard.

the original excel table looks pretty much the same with the table you see on the lower left corner.

I want to figure out a way to effectively show the cost of each project.

my dimension is "Project Name" and I set my expression as

there is one problem with my approach. It works fine when all the projects share same star and end date for all of the resources.

but if person A and person B in same project have different start and end date for the project, it does not give me the correct calculation of overall project cost and I know that it is because I set my dimension as "Project Name." and I know that it is because I have set the expression as Max([End Date]) - Min([Start Date]). it will pick up the latest end date and earliest start date.

can anyone help me with a way to calculate the cost of each project correctly even though every individual involved in the project does not have the same duration of involvement into the project and when everybody has different rate?

Thanks!!

1 Solution

Accepted Solutions
Not applicable
Author
 =sum(   ([End Date]-[Start Date]) *([Pay per day]) )this worked!
6 Replies
Master III

Maybe try something like this..

=Aggr(Max([End Date])-Min([Start Date])*sum([Pay per day]),[Project Name])

Partner - Champion III

Apparently, the fact that employees that participate in a particular project can have different start dates, end dates and hourly rates makes the aggregation expression a bit more complex.

=sum(aggr(sum((alt([End Date], today())-[Start Date])*[Pay per day])), [Employee Name], [Project Name]))

Maybe you should post an example document with a representative data set for us to experiment on.

Peter

Not applicable
Author

sorry it did not work. I am sure there should be a way but little complicated to come up with it...

Not applicable
Author

sorry it did not work. I am sure there should be a way but little complicated to come up with it...

Not applicable
Author
 Name Role Project Name Project Owner Timing Start Date End Date Pay per hour Pay per day Daryl Dixon English Ongoing Paul Ongoing 4/28/2016 14 112 Rick Grimes English Project A David Term 4/28/2016 5/28/2016 15 120 GlennRhee English Project A David Term 4/28/2016 5/28/2016 16 128 Maggie Greene English Ongoing David Ongoing 4/28/2016 17 136 Michonne TL Ongoing David Ongoing 4/28/2016 18 144 Carol Peletier KOR II Project B Paul Term 5/28/2016 6/28/2016 19 152 Carl Grimes English Project B Paul Term 5/28/2016 6/28/2016 20 160 Beth Greene English Ongoing Paul Ongoing 4/28/2016 21 168 Sasha Williams English Ongoing Paul Ongoing 4/28/2016 22 176 Abraham Ford JPN II Ongoing David Ongoing 4/28/2016 23 184 Andrea TL Ongoing David Ongoing 4/28/2016 24 192 Lori Grimes JPN II Project C Paul Term 4/28/2016 6/28/2016 25 200 Rosita Espinosa English Project C Paul Term 4/28/2016 7/28/2016 26 208 Tyreese English Project C Paul Term 4/28/2016 7/28/2016 27 216 Eugene Porter English Project D Paul Ongoing 4/28/2016 5/28/2016 28 224 Eugene Porter English Project E Paul Ongoing 5/28/2016 6/28/2016 28 224 Eugene Porter English Project F Paul Ongoing 6/28/2016 7/28/2016 28 224

Here is my example!! thank you!!

Not applicable
Author
 =sum(   ([End Date]-[Start Date]) *([Pay per day]) )this worked!
Community Browser