Help w SumX using calculated column (multiplication)

[PowerPaul]

Hi all I have a data model which Im using to calculate costs in an excel data model.

Each employee is assigned a % they are on a scrum team. I have the daily rate for each employee and multiply that by % to get the adjusted daily rate. When I sum this column implicitly or explicitly with a measure I am getting the incorrect total for each scrum team. I am assuming I need to use SumX to get the correct total per scrum team however I cannot get it to work. 

The adjusted daily rate column multiplies % by daily rate. 

I need to sum this column to show all the scrum team rates by team and employee. 

The subtotal rows for each scrum team are not displaying correctly but the employee rows are correct.

AdjDailyRate:=SUMX(Scrum__W_Rates,SUM(Scrum__Rates[Adjusted Daily Rate]))

Example: the highlighted cell should equal 14,996.25. 

Thank you

[tamerj1 · Accepted solution]

Hi @PowerPaul 

please try

AdjDailyRate:=SUMX(Scrum__W_Rates,Scrum__Rates[Adjusted Daily Rate])

however, the normal sum should also work. What do you have on the rows of the pivot table?

[PowerPaul]

Hi tamerj1, the regular sum has worked. I am not sure why it didnt work the last time maybe because I used calculate(sum but with no filters it should have been the same. 

I find Excel to be very buggy and always has issues so maybe there is something wrong w my version of excel. 

My pivot table rows consist of scrum team name followed by employee name. 

This gives me the view of each scrum team members, the % and cost per team. 

I appreciate the help. 

Thanks

[tamerj1]

@PowerPaul 
I don't work so often with Power Pivot but it does behave differently compared to Power Bi that is true.