Forum Discussion

IntaBruce's avatar
IntaBruce
Icon for Resolver I rankResolver I
3 years ago

Wrong total?

I have a table of staff which contains their day rates and a table of allocations which contains numbers of days for a given staff member to a given project in a given period. I want a measure that ...
IntaBruce's avatar
IntaBruce
Icon for Resolver I rankResolver I
3 years ago

Ok, so how cool is ChatGPT!  After 2 hours of no replies but 28 views I thought I'd give the AI a test, and it gave me the solution in seconds!!  Feel free to critique but it worked for me 🙂

 

Here's the AI response to the exact same question:

To calculate the cost per person per project per period correctly, you need to use a measure that considers the aggregation at the appropriate levels. In this case, you need to calculate the cost for each allocation (staff member to project in a given period) and then aggregate those individual allocation costs correctly.

To do this, you can use an iterator function like SUMX to iterate over each allocation and calculate the cost for that specific allocation. Here's how you can write the measure:

 

CostPerAllocation = SUMX ( Allocations, Allocations[Days] * LOOKUPVALUE(Resources[Rate], Resources[Staff], Allocations[Staff]) )

Let's break down the measure:

  1. SUMX: This is the iterator function that iterates over each row in the Allocations table.
  2. Allocations[Days]: This retrieves the number of days from the current row of the Allocations table.
  3. LOOKUPVALUE: This function looks up the corresponding rate from the Resources table based on the Staff member in the current row of the Allocations table.
  4. Allocations[Staff]: This provides the Staff member from the current row of the Allocations table to the LOOKUPVALUE function.

    The measure calculates the cost for each individual allocation (Staff member to project in a given period) and then you can use this measure to correctly calculate the total cost per person per project per period.