cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper II

## 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 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?

Helper II

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.

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors