Issue with SUMX and Row Duplicates

[ITManuel]

Hi,

I don't understand the behaviour of my measure when iterating over a Table.

I have the following tables:

Hours --> containing work hour registrations:

DEPARTMENTID  EMPLOYEEID  WORKPROCESSID   DATEENTRY               DURATION

AD/OF	E1	E01	30.09.2020 00:00	08:00
AD	E1	E01	30.09.2020 00:00	08:00

HourlyRatesold --> containing Hourlyrates of individual departments

DEPARTMENTID    HOURLYRATE

AD/OF	30
AD	30

I have a measure which computes the duration in working time in hours.

Hours = SUM ( Hours[DURATION] ) * 24

And tried two different variations both behaving the same, for calculating the labour costs. 

LabourCostold = 
SUMX (
    Hours,
    [Hours]
        * MAXX (
            FILTER ( HourlyRatesold, HourlyRatesold[DEPARTMENTID] = Hours[DEPARTMENTID] ),
            HourlyRatesold[HOURLYRATE]
        )
)

SUMX (
    Hours,
    [Hours]
        * SELECTCOLUMNS(
            FILTER ( HourlyRatesold, HourlyRatesold[DEPARTMENTID] = Hours[DEPARTMENTID] ),
            "HR",HourlyRatesold[HOURLYRATE]
        )
)

Since the HourlyRatesold Table also contains different Hourly rates for each year and department the DEPARTMENTID column does not contain unique values and thus it would result in a many to many relationship with the Hours table, consequently there is no relationship in between the two tables in the model. For this example the HourlyRatesold Table does not contain different Hourly rates for different years.

For the data indicated above the calculation works fine, resulting in 16 for the Hours measure and 480 for the LabourCostold measure.

However when the data is the following: 

Hours --> containing work hour registrations:

DEPARTMENTID  EMPLOYEEID  WORKPROCESSID   DATEENTRY               DURATION

AD/OF	E1	E01	30.09.2020 00:00	08:00
AD/OF	E1	E01	30.09.2020 00:00	08:00

(Two identical rows which can appear in the Hours table)

HourlyRatesold --> containing Hourlyrates of individual departments

DEPARTMENTID    HOURLYRATE

AD/OF	30
AD	30

The measure hours still return 16 correctly, but the LabourCostold measure return 960, the double amount. 

I don't understand why the LabourCostold measure returns the double amount since I would expect it does the same for both rows --> 8*30 + 8*30 = 480

Thanks in advance

[v-eqin-msft]

Hi @ITManuel ,

The HOYRLYRATE seems to be based on the max rate of last year(,today is 2022, so the last year is 2021)

I used the following to get the output——8*30 +8*30, please check

Measure = 
var _rate=MAXX(FILTER(ALL('HOURLYRATES'),[DEPARTMENTID]=MAX('HOURLYRATES'[DEPARTMENTID])&& [YEAR]=YEAR(TODAY())-1),[HOURLYRATE])
return SUMX('Hours2',[Hours2]*_rate)

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ITManuel]

Hi @v-eqin-msft ,

The Hourlyrate Table contains the Hourly rate for each department and actual year. Rates of 2020 in the Hourly rates table should be applied for 2020 Datenentries in the Hours table.

Since there is data in the Hours table also before 2020 the measure should look for the last Year available in the Hourlyrates table and return that Hourly rate.

The issue is in the Hours3 table in which the is a row duplicate. My measures as well as your return double the amount of what would be correct for the 30.09.2020 as the are two identical rows for that date. 

The issue seems to be that the MAXX or LOOKUP does sum up the Hourlyrate for that date.

Updated sample file under the link below:

https://we.tl/t-V4rzqQ6oTV

Br

[amitchandak]

@ITManuel , using this part of the code, you should create a new column in the table

Rate = MAXX (
FILTER ( HourlyRatesold, HourlyRatesold[DEPARTMENTID] = Hours[DEPARTMENTID] ),
HourlyRatesold[HOURLYRATE]
)

But if you have more information you can find better value

You can create another column in first table 

hour([DURATION]) * [Rate]

[ITManuel]

Hi @amitchandak ,

I would like to avoid the addition of columns if not strictly required. In any case I tried your proposed solution but the behaviour remains the same. 

As soon as the two rows are duplicates, the result is double the correct amount. 

DEPARTMENTID	EMPLOYEEID	WORKPROCESSID	DATEENTRY	DURATION
AD/OF	E1	E01	30.09.2020 00:00	08:00
AD	E1	E01	30.09.2020 00:00	08:00

DEPARTMENTID	HOURLYRATE
AD/OF	30
AD	30

Result of the LabourCost calculation = 480, so correct

With this data containing duplicates in the Hours Table, the result is the double amount.

DEPARTMENTID	EMPLOYEEID	WORKPROCESSID	DATEENTRY	DURATION
AD/OF	E1	E01	30.09.2020 00:00	08:00
AD/OF	E1	E01	30.09.2020 00:00	08:00

DEPARTMENTID	HOURLYRATE
AD/OF	30
AD	30

Result of the LabourCost calculation = 960

[ITManuel]

Hi, any help on this please?

I slightly changed the data and calculations and also tried a variation of the code with lookupvalue.

Original measure:

LabourCost1.1 = 
VAR _Result =
    SUMX (
        Hours1,
        [Hours1]
            *
            VAR _HourlyRate =
                MAXX (
                    FILTER (
                        HourlyRates,
                        HourlyRates[DEPARTMENTID] = Hours1[DEPARTMENTID]
                            && HourlyRates[YEAR] = YEAR ( Hours1[DATEENTRY] )
                    ),
                    HourlyRates[HOURLYRATE]
                )
            VAR _HourlyRateNoValuePast =
                MAXX (
                    FILTER (
                        HourlyRates,
                        HourlyRates[DEPARTMENTID] = Hours1[DEPARTMENTID]
                            && HourlyRates[YEAR] = MIN ( HourlyRates[YEAR] )
                    ),
                    HourlyRates[HOURLYRATE]
                )
            VAR _HourlyRateNoValueFuture =
                MAXX (
                    FILTER (
                        HourlyRates,
                        HourlyRates[DEPARTMENTID] = Hours1[DEPARTMENTID]
                            && HourlyRates[YEAR] = MAX ( HourlyRates[YEAR] )
                    ),
                    HourlyRates[HOURLYRATE]
                )
            RETURN
                IF (
                    NOT ( ISBLANK ( _HourlyRate ) ),
                    _HourlyRate,
                    SWITCH (
                        TRUE,
                        YEAR ( Hours1[DATEENTRY] ) < MIN ( HourlyRates[YEAR] ), _HourlyRateNoValuePast,
                        YEAR ( Hours1[DATEENTRY] ) < MIN ( HourlyRates[YEAR] ), _HourlyRateNoValueFuture
                    )
                )
    )
RETURN
    _Result

Variation with lookupvalue:

LabourCost1.2 = 
VAR _Result =
    SUMX (
        Hours1,
        [Hours1]
            *
        VAR _HourlyRate =
            LOOKUPVALUE ( 
                HourlyRates[HOURLYRATE],
                HourlyRates[DEPARTMENTID],Hours1[DEPARTMENTID],
                HourlyRates[YEAR], YEAR ( Hours1[DATEENTRY] )
            )
            VAR _HourlyRateNoValuePast =
            LOOKUPVALUE ( 
                HourlyRates[HOURLYRATE],
                HourlyRates[DEPARTMENTID],Hours1[DEPARTMENTID],
                HourlyRates[YEAR], MIN ( HourlyRates[YEAR] )
            )
            VAR _HourlyRateNoValueFuture =
            LOOKUPVALUE ( 
                HourlyRates[HOURLYRATE],
                HourlyRates[DEPARTMENTID],Hours1[DEPARTMENTID],
                HourlyRates[YEAR], MAX ( HourlyRates[YEAR] )
            )
            RETURN
                IF (
                    NOT ( ISBLANK ( _HourlyRate ) ),
                    _HourlyRate,
                    SWITCH (
                        TRUE,
                        YEAR ( Hours1[DATEENTRY] ) < MIN ( HourlyRates[YEAR] ), _HourlyRateNoValuePast,
                        YEAR ( Hours1[DATEENTRY] ) < MIN ( HourlyRates[YEAR] ), _HourlyRateNoValueFuture
                    )
                )
    )
RETURN
    _Result

I tested these 2 measures on 3 different Hours Tables with 2 rows only. Only the Hours3 Table contains duplicate rows.

HOURLYRATES Table

HOURS1 Table

HOURS2 Table

HOURS3 Table

For each of these 3 Hours Tables I tested both measure variations, returning the following result.

The total duration in all Hours Tables i 16 hours and the Hourly rate to be considered is 25€/h. Consequently the correct result for labour cost is 400€, which is correct for the tables (Hours 1 and Hours 2 ) with no duplicate rows. The measures for Table Hours 3, containing duplicate rows, return 800€ as a result, which is incorrect.

The attached the PBix sample file under the link below:

https://we.tl/t-CnlYp5QlIQ

Best regards