Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Problem with showing multiplying hours per pay rate.

Good day,

 

I'm quite new with Power BI, I'm having a small problem displaying the correct information.

 

I got a formula that works but for some reason when its one user its mulitplying something els.

For example.

 

 

With two users the Paid hours are showing the correct ammount of R5 250

But.

 

I have no idea where its getting the R132 000, It should be displaying the R5500

I suspect I need to add something on to the formula but unsure.

 

These are the two formulas I'm using.

 

"

Pay Measure 1 = SUM( 'Department Projects'[Hours] ) * SUM( 'Hourly Rate'[Hourly ] )
"
 
" Paid Hours =
IF (
HASONEVALUE ( 'Department Projects'[Employee ID] ),
[Pay Measure 1],
SUMX ( 'Department Projects', [Pay Measure 1])
)
"
 
Thank you.
Crystal
Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    4 years ago

    Hi, Anonymous ;

    I tried it and the results is correct.(here have relationship by Employee ID)

    then :

     

    Pay Measure 1 = SUM('Department Projects'[Hours])*SUM('Hourly Rate'[Hourly ])
    Measure 2 = IF(HASONEVALUE('Department Projects'[Employee ID]),[Pay Measure 1], SUMX('Department Projects',[Pay Measure 1]))

     

    The final output is shown below:

    so you could check the different from yours and my data model,and the more details about your data.


    Best Regards,
    Community Support Team_ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous , If these tables are related and hourly rate is on one side 

     

    Pay Measure 1 = SUMX( 'Department Projects' ,'Department Projects'[Hours]  * related( 'Hourly Rate'[Hourly ] ) )

     

    else try

    sumx( values( 'Department Projects'[Employee ID] ), calculate( SUM( 'Department Projects'[Hours] ) * SUM( 'Hourly Rate'[Hourly ] )) )

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi amitchandak,

       

      Thanks for the reply.

       

      The formula works but still having the same problem. I tried moving the hourly rate to.

       

       

      • amitchandak's avatar
        amitchandak
        Super User
        4 years ago

        Anonymous ,
        Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Community Support
    4 years ago

    Hi, Anonymous ;

    I tried it and the results is correct.(here have relationship by Employee ID)

    then :

     

    Pay Measure 1 = SUM('Department Projects'[Hours])*SUM('Hourly Rate'[Hourly ])
    Measure 2 = IF(HASONEVALUE('Department Projects'[Employee ID]),[Pay Measure 1], SUMX('Department Projects',[Pay Measure 1]))

     

    The final output is shown below:

    so you could check the different from yours and my data model,and the more details about your data.


    Best Regards,
    Community Support Team_ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.