Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello, everybody,
While doing a mashup of Jira logged time data and ERP data I ran against the wall with a seemingly simple calculation of planned costs.
I take the average cost for each employee over the last 3 months and try to multiply it by the hours that each of them were allocated for each ERP project. Just multiplying the sums is not an option because each employee has a different cost. Normally a SUMX would work, but in this case I get blank results. Is there any way to get around this? Maybe CALCULATETABLE, SUMMARIZE or something similar?
Any help would be appreciated
The measures involved and the data model:
Total Planned Cost = SUMX('Fact', 'Fact'[PlannedHours] * [Average Unit Cost])
Average Unit Cost = CALCULATE(SUM('Fact'[Cost (Balance)])/[Available hours], FILTER( ALL(DimDate), DimDate[Last3]=1))
Available hours is quite complex, but I'll put it after the screenshot.
Available hours = SUMX(DimEmployeeHistory,
IF(DimEmployeeHistory[Statusas]="nedirba",0,
(IF(DimEmployeeHistory[StartDate]<=MIN(DimDate[Date]) && DimEmployeeHistory[EndDate]>=MIN(DimDate[Date]) && DimEmployeeHistory[EndDate]<=MAX(DimDate[Date]), (CALCULATE(SUM(DimDate[IsWorkday]), DATESBETWEEN(DimDate[Date], MIN(DimDate[Date]), DimEmployeeHistory[EndDate]))-1)*8*DimEmployeeHistory[Etatas],
(IF(DimEmployeeHistory[StartDate]>=MIN(DimDate[Date]) && DimEmployeeHistory[EndDate]>=MIN(DimDate[Date]) && DimEmployeeHistory[EndDate]<=MAX(DimDate[Date]), (CALCULATE(SUM(DimDate[IsWorkday]), DATESBETWEEN(DimDate[Date], DimEmployeeHistory[StartDate], DimEmployeeHistory[EndDate]))-1)*8*DimEmployeeHistory[Etatas],
(IF(DimEmployeeHistory[StartDate]>=MIN(DimDate[Date]) && DimEmployeeHistory[StartDate]<=MAX(DimDate[Date]) && (DimEmployeeHistory[EndDate]>=MAX(DimDate[Date]) || ISBLANK(DimEmployeeHistory[EndDate])), (CALCULATE(SUM(DimDate[IsWorkday]), DATESBETWEEN(DimDate[Date], DimEmployeeHistory[StartDate], MAX(DimDate[Date]))))*8*DimEmployeeHistory[Etatas],
(IF(DimEmployeeHistory[StartDate]<=MIN(DimDate[Date]) && (DimEmployeeHistory[EndDate]>=MAX(DimDate[Date]) || ISBLANK(DimEmployeeHistory[EndDate])), (CALCULATE(SUM(DimDate[IsWorkday]), DATESBETWEEN(DimDate[Date], MIN(DimDate[Date]), MAX(DimDate[Date]))))*8*DimEmployeeHistory[Etatas],
0))))))))))
@Anonymous
Though you have posted the table relationships, however it is still difficult to find why the measures don't return the expected output. Could you please post a sample pbix?
Or you may try to do the self troubleshooting. Try to make the formula more readable and check the result one by one. eg, if the Available hours doesn't work, try to eliminate the nested IFs and add and check them one by one.
Available hours = SUMX ( DimEmployeeHistory, IF ( DimEmployeeHistory[Statusas] = "nedirba", 0, ( IF ( DimEmployeeHistory[StartDate] <= MIN ( DimDate[Date] ) && DimEmployeeHistory[EndDate] >= MIN ( DimDate[Date] ) && DimEmployeeHistory[EndDate] <= MAX ( DimDate[Date] ), ( CALCULATE ( SUM ( DimDate[IsWorkday] ), DATESBETWEEN ( DimDate[Date], MIN ( DimDate[Date] ), DimEmployeeHistory[EndDate] ) ) - 1 ) * 8 * DimEmployeeHistory[Etatas], ( IF ( DimEmployeeHistory[StartDate] >= MIN ( DimDate[Date] ) && DimEmployeeHistory[EndDate] >= MIN ( DimDate[Date] ) && DimEmployeeHistory[EndDate] <= MAX ( DimDate[Date] ), ( CALCULATE ( SUM ( DimDate[IsWorkday] ), DATESBETWEEN ( DimDate[Date], DimEmployeeHistory[StartDate], DimEmployeeHistory[EndDate] ) ) - 1 ) * 8 * DimEmployeeHistory[Etatas], ( IF ( DimEmployeeHistory[StartDate] >= MIN ( DimDate[Date] ) && DimEmployeeHistory[StartDate] <= MAX ( DimDate[Date] ) && ( DimEmployeeHistory[EndDate] >= MAX ( DimDate[Date] ) || ISBLANK ( DimEmployeeHistory[EndDate] ) ), ( CALCULATE ( SUM ( DimDate[IsWorkday] ), DATESBETWEEN ( DimDate[Date], DimEmployeeHistory[StartDate], MAX ( DimDate[Date] ) ) ) ) * 8 * DimEmployeeHistory[Etatas], ( IF ( DimEmployeeHistory[StartDate] <= MIN ( DimDate[Date] ) && ( DimEmployeeHistory[EndDate] >= MAX ( DimDate[Date] ) || ISBLANK ( DimEmployeeHistory[EndDate] ) ), ( CALCULATE ( SUM ( DimDate[IsWorkday] ), DATESBETWEEN ( DimDate[Date], MIN ( DimDate[Date] ), MAX ( DimDate[Date] ) ) ) ) * 8 * DimEmployeeHistory[Etatas], 0 ) ) ) ) ) ) ) ) ) )
User | Count |
---|---|
94 | |
78 | |
73 | |
63 | |
60 |
User | Count |
---|---|
108 | |
101 | |
77 | |
63 | |
61 |