Forum Discussion

ITManuel's avatar
ITManuel
Icon for Responsive Resident rankResponsive Resident
4 years ago

Issue with SUMX and Row Duplicates

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  WORKPROC...
Need Help
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

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's avatar
    ITManuel
    Icon for Responsive Resident rankResponsive Resident
    4 years ago

    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/OFE1E0130.09.2020 00:0008:00
    ADE1E0130.09.2020 00:0008:00

     

    DEPARTMENTID  HOURLYRATE
    AD/OF30
    AD30

     

    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/OFE1E0130.09.2020 00:0008:00
    AD/OFE1E0130.09.2020 00:0008:00

     

    DEPARTMENTID  HOURLYRATE
    AD/OF30
    AD30

     

    Result of the LabourCost calculation = 960

    • ITManuel's avatar
      ITManuel
      Icon for Responsive Resident rankResponsive Resident
      4 years ago

      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