Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Problem with multiplying hours per pay rate

Hello! So I am working on a project and trying to mutliply employee hours by their pay rate based on pay grade but I seem to be having an issue. I've tried using SUMX * SUM but its giving me widly ma...
Need Help
  • Icey's avatar
    Icey
    6 years ago

    Hi Anonymous ,

     

    Sorry, it was complicated by me. Just try this:

     

    1. Create [Pay Measure 1].

     

    Pay Measure 1 = SUM( 'Hours'[Hours] ) * SUM ( 'PAY RATES'[Hrly Rate] )

     

     

    2. Create [Pay Measure 2].

     

    Pay Measure 2 = 
IF (
    HASONEVALUE ( Hours[Employee ID] ),
    [Pay Measure 1],
    SUMX ( Hours, [Pay Measure 1] )
)

     

     

     

    Best Regards,

    Icey

     

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

Icey's avatar
Icey
Community Support
6 years ago

Hi Anonymous ,

 

Create the measure like so:

 

Pay Measure 2 = 
VAR table_ =
    SUMMARIZE (
        Hours,
        Hours[Employee ID],
        Hours[Pay Grade],
        Hours[Hours],
        'PAY RATES'[Hrly Rate],
        "Sum_", [Hours] * [Hrly Rate]
    )
RETURN
    IF (
        HASONEVALUE ( Hours[Employee ID] ),
        SUMX ( 'Hours', 'Hours'[Hours] ) * SUM ( 'PAY RATES'[Hrly Rate] ),
        SUMX ( table_, [Sum_] )
    )

 

Best Regards,

Icey

 

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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hello, 

 

Thanks for the reponse and help! I tried using the measure you provided but I am unable to select 'PAY RATES' table it is not listed when I try to add the last line in SUMMARIZE. I noticed your pbix has a 1*1 relationship but mine is MANY*MANY is that why?  Also worth noting that on my HOURS table each employee has multiple rows of hours

 

PAY RATES'[Hrly Rate],

 

  • Icey's avatar
    Icey
    Community Support
    6 years ago

    Hi Anonymous ,

     

    Sorry, it was complicated by me. Just try this:

     

    1. Create [Pay Measure 1].

     

    Pay Measure 1 = SUM( 'Hours'[Hours] ) * SUM ( 'PAY RATES'[Hrly Rate] )

     

     

    2. Create [Pay Measure 2].

     

    Pay Measure 2 = 
IF (
    HASONEVALUE ( Hours[Employee ID] ),
    [Pay Measure 1],
    SUMX ( Hours, [Pay Measure 1] )
)

     

     

     

    Best Regards,

    Icey

     

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thank you so much this is exactly what I needed!