Total Incorrect When Using Multiple Columns

[GlynMThomas]

Hi,

I have this model setup.

I have this measure:

Admin Estimate Days =

(SUM('WorkTimeData'[Days]) - SUM('Holidays'[Holiday Duration (Days)]) - SUM(Absence[Sick Duration (Days)])) * SUM('Name mapping'[Admin pc])

Because the Admin PC column is a fraction this means the total is incorrect in my table:

I would normally use a column to do this calc and then it would automatically do that calculation first then sum the values up like I want it to. But power bi won't allow me to pull the other columns into one place using RELATED unless it's in a measure which means the calc happens after the aggregation giving me an incorrect total.

How would I perform the caculation first before the aggregation?

[jgeddes]

Try this calculated column in the Name mapping table

Calculated Column =

var _holidayDays =

CALCULATE(

    SUM(Holidays[Holiday Duration (Days)]),

    FILTER(Holidays, Holidays[Holiday PeopleHR Name] = Name mapping[PeopleHR Name])

)

var _absenceDays =

CALCULATE(

    SUM(Absence[Sick Duration (Days)]),

    FILTER(Absence, Absence[Absence PeopleHR Name] = Name mapping[PeopleHR Name])

)

var _workDays =

CALCULATE(

    SUM(WorkTimeData[Days]),

    FILTER(WorkTimeData, WorkTimeData[Name] = Name mapping[PeopleHR Name])

)

return

( _workDays - _holidayDays - _absenceDays) * nameTable[admin pc]

[GlynMThomas · Accepted solution]

Thanks, that kind of worked but introduced some other issues in the aggregation. I managed to solve it in the end with a summarised table combining all the values and aggregating them into 1 row.