Forum Discussion
SUMX Function not working calculating per row
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
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.
4 Replies
- MFelix
Super User
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.
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])