Struggling with a SUMX that involves a measure

[Anonymous]

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))))))))))

[Eric_Zhang]

@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
                                    )
                                )
                            )
                        )
                    )
                )
            )
        )
    )
)