SUMX Function not working calculating per row

[Anonymous]

Hi PowerBI Experts. I'm working on a POC project.

Sumx Function is not working as expected I might be missing some calculations.

Table1: timesheetCC table

It contains the actual hours, date, Resource ID, Rate,Project ID,

I was able to get the correct hours and correct rates per resourceID in the cross table

Expected result:

Project 1	25,028.29
Resource1	5363.1
Resource2	1870
Resource3	5711.55
Resource4	9683.88
Resource5	2399.76
Resource6	2622

on the project level I'm getting wrong result but on resource level or row level the calculation is correct

Current result:

Here's the actual cost formula

Actual Cost = SUMX('Timesheet CC',[Actual Hrs])*MAX('Timesheet CC'[Rate])

I am using a calendar table with calendar auto as date dimension connected to the date from timesheetCC. Not sure if this has impacts.

Thanks!

[Anonymous]

I was not able to make it work but probably I'm just not sure how to adjust it as I'm getting error 

but I found another solution here

Solved: Problem with multiplying hours per pay rate - Microsoft Power BI Community

Pay Measure 1 = SUM( 'Hours'[Hours] ) * SUM ( 'PAY RATES'[Hrly Rate] )

Pay Measure 2 = 
IF (
    HASONEVALUE ( Hours[Employee ID] ),
    [Pay Measure 1],
    SUMX ( Hours, [Pay Measure 1] )
)

[MFelix]

Sorry,

Had an additional separator. Try this

Actual Cost = SUMX(ADDCOLUMNS('Timesheet CC',"ActualCost",[Actual Hrs]*MAX('Timesheet CC'[Rate])), [ActualCost])

[Anonymous]

I tried it also not working same output as my original one.

Red circle is this formula

Actual Cost CC Test = SUMX(ADDCOLUMNS('Timesheet CC',"ActualCost",[Actual Hrs]*MAX('Timesheet CC'[Rate])), [ActualCost])

Appreciate your help thanks!

[MFelix · Accepted solution]

Hi @Anonymous ,

When you pick up the MAX at the Actual Cost in the project level you are getting the maximum value for all the lines in that project in this case try to change your measure to:

Actual Cost = SUMX(ADDCOLUMNS('Timesheet CC',"ActualCost",[Actual Hrs])*MAX('Timesheet CC'[Rate])), [ActualCost])

Just did this without testing it may need some adjustments.