Calculate Total Cost of multiple projects by resource

[wkeicher]

I am looking for assistance in summing the total cost of a resource accross all projects based on hours X rate (Where rates are differentdepending on project).

Table of hours entered by Resource, date, projectid - 

Billable Hours = SUM(ActivityUpdates[RegularHours]) + SUM(ActivityUpdates[OTHours])

Table of rates by projectid and resource - Need to calculate the hours for given project multiplied by the resource rate for that project and sum all by resource.

Result - resource - total hours - total cost

[v-shex-msft]

HI @wkeicher,

Please share some dummy data with same data structure and expected result, it will help for test and coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[wkeicher]

So I am Using a Matrix Visual with a calculated field (Billable Cost) - The sum of the detail in yellow is not accurate. I am trying to aggreagte the values by ticket Numberand resource up to the resource level. A Table Visual shows all the values correctly but doesn't aggregate at the resource level.

 

[wkeicher]

More info - 3 tables - All Joined by Ticket Number

Activity Updates (Conatins entries of hours for each time entry against a ticket)

Tickest Resources (Contains HourlyCost by Resource for each Ticket)

Tickets - (Contains Ticket Details)

[v-shex-msft]

HI @wkeicher,

It seems like your records have been effect by activated relationship, maybe you can try to use USERELATIONSHIP function to calculate based specific relationship mapping.

USERELATIONSHIP in Calculated Columns 

Regards,

Xiaoxin Sheng

[wkeicher]

This is defnately helpful, but it also seems that I cannot Sum the ActivityUpdated[Hours] * TicketResources(HourlyCost]?

ActivityUpdates[Hours] needs tobe summed by TicketNo, then Multiply by the TicketResource][HoulyCost] for that same specific TicketNo

There are two issues - Sum ActivityUpdated[Hours] by TicketNumber and then by resource

Then Multiply the result by Resources Rate for that same TicketN.

[wkeicher]

Billable Cost Measure is what is troubling me I think.

Billable Cost = CALCULATE(SUMX(ActivityUpdates,ActivityUpdates[Hours]) * Average(TicketResources[HourlyCost]), USERELATIONSHIP(PSDRoles[Resource Name],TicketResources[Resource]))

I cant eem to get the calcuation to calculate by just the field.

[v-shex-msft]

Hi @wkeicher,

Can you please share some dummy data with minmium core data stucurtre for test? It will help to test and coding formulas.

How to Get Your Question Answered Quickly  

In addition, please double check your formal to confirm you have activated the corresponding relationship mapping in your calculations expressions.

Billable Cost =
CALCULATE ( SUM ( ActivityUpdates[Hours] ), ActivityUpdates )
    * CALCULATE (
        AVERAGE ( TicketResources[HourlyCost] ),
        USERELATIONSHIP ( PSDRoles[Resource Name], TicketResources[Resource] )
    )

Regards,

Xiaoxin Sheng

[wkeicher]

I think the issue is the use of Average. or Sum in the calculation. What I need is the actual cost summed in the Matrix. The value should be$776 + 34,425 = 35,201  

[piyushszope]

To calculate the hours for a given project multiplied by the resource rate you can use the SumX and the Related function together. For example, 

= SUMX(ResorceHrsTable,ResorceHrsTable[Hrs] * RELATED(RatesTable[Cost]))

**Important to create a relation between both the tables 

[wkeicher]

Thank you - How do I create a relation between the two tables?

[piyushszope]

For more details, you can review this video by Alberto Ferrari. I hope this will clear all your doubts regarding the Related function and the relationships.