Wrong total?

IntaBruce — Wed, 26 Jul 2023 15:42:59 GMT

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 will calculate the cost per person per project per period so I can display it in a table

= sum(Allocations[Days]) * SUM(Resources[Rate]) works fine at the resource line level but the total is incorrect for all resources. I think this is because this measure is summing the rates for all resources before multipling by the number of days, instaed of adding up all the line leve values.

Does that make sense? And how do I write a measure to fix it?

Re: Wrong total?

IntaBruce — Wed, 26 Jul 2023 18:09:39 GMT

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:

SUMX: This is the iterator function that iterates over each row in the Allocations table.
Allocations[Days]: This retrieves the number of days from the current row of the Allocations table.
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.
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.

topic Re: Wrong total? in DAX Commands and Tips

Wrong total?

Re: Wrong total?