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  WORKPROCESSID   DATEENTRY               DURATION

AD/OFE1E0130.09.2020 00:0008:00
ADE1E0130.09.2020 00:0008:00

 

HourlyRatesold --> containing Hourlyrates of individual departments

DEPARTMENTID    HOURLYRATE

AD/OF30
AD30

 

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

 

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

 

HourlyRatesold --> containing Hourlyrates of individual departments

DEPARTMENTID    HOURLYRATE

AD/OF30
AD30

 

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

 

Need Help

5 Replies

  • 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

         

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

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

      Hi Anonymous ,

       

      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