cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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.

 

CrystalHax_4-1642056830440.jpeg

 

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

But.

 

CrystalHax_5-1642056851865.jpeg

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
1 ACCEPTED SOLUTION
v-yalanwu-msft
Community Support
Community Support

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:

vyalanwumsft_0-1642397338287.png

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.

View solution in original post

5 REPLIES 5
v-yalanwu-msft
Community Support
Community Support

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:

vyalanwumsft_0-1642397338287.png

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.

amitchandak
Super User
Super User

@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 ] )) )

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
!! Subscribe to my youtube Channel !!
Anonymous
Not applicable

Hi amitchandak,

 

Thanks for the reply.

 

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

 

 

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

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
!! Subscribe to my youtube Channel !!
Anonymous
Not applicable

 

I tried sharing the Pbix and the Excel file but can't

 

The Hourly Rate

 

Employee IDHourly SalaryDepartment Code
IA001R250R42 000RC001
IA002R250R42 000TS001
IA003R250R42 000AP001
IA004R250R42 000AP001
IA005R250R42 000RC001
IA006R250R42 000RC001
IA007R250R42 000AC001
IA008R250R42 000AC001
IA009R250R42 000AC001
IA010R250R42 000BD001
IA011R250R42 000AC001
IA012R250R42 000AP001
IA013R250R42 000DS001
IA014R250R42 000DS001
IA015R250R42 000OP001
IA016R250R42 000OP001
IA017R250R42 000AC001
IA018R250R42 000OP001
IA019R250R42 000TS001
IA020R250R42 000TS001
IA021R250R42 000TS001
IA022R250R42 000TS001
IA023R250R42 000TS001
IA024R250R42 000DS001

 

Department Projects with Hours

ProjectDescriptionDepartment CodeEmployee IDStart DateStart TimeEnd DateEnd TimeDuration (h)Hours
SCREEN CVSCREEN CVAC001IA0092022/01/1213:002022/01/1217:004:004:00
COLD CALLING / COMPANYCOLD CALLING / COMPANYAC001IA0092022/01/128:002022/01/1212:004:004:00
SCREEN CVSCREEN CVAC001IA0092022/01/1113:002022/01/1117:004:004:00
SCREEN CVSCREEN CVAC001IA0092022/01/1013:002022/01/1017:004:004:00
COLD CALLING / COMPANYCOLD CALLING / COMPANYAC001IA0092022/01/118:002022/01/1112:004:004:00
COLD CALLING / COMPANYCOLD CALLING / COMPANYAC001IA0092022/01/108:002022/01/1012:004:004:00
POWER BIPOWER BIDS001IA0242022/01/1213:002022/01/1217:004:004:00
POWER BIPOWER BIDS001IA0242022/01/128:002022/01/1212:004:004:00
POWER BIPOWER BIDS001IA0242022/01/119:002022/01/1116:007:007:00
POWER BIPOWER BIDS001IA0242022/01/109:002022/01/1016:007:007:00
INTERNAL ITINTERNAL ITDS001IA0242022/01/108:002022/01/109:001:001:01
FN-0002 TRAININGFN-0002 TRAININGDS001IA0142022/01/117:102022/01/1116:008:508:51
FN-0002 TRAININGFN-0002 TRAININGDS001IA0142022/01/109:002022/01/1016:007:007:01

 

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through June10th!

Power BI Dev Camp Session 22 without aka link and time 768x460.jpg

Check it Out!

Watch Session 22 Ted's Dev Camp along with past sessions!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!

Top Kudoed Authors