Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
"
Solved! Go to Solution.
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.
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.
@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 ] )) )
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.
I tried sharing the Pbix and the Excel file but can't
The Hourly Rate
Employee ID | Hourly | Salary | Department Code |
IA001 | R250 | R42 000 | RC001 |
IA002 | R250 | R42 000 | TS001 |
IA003 | R250 | R42 000 | AP001 |
IA004 | R250 | R42 000 | AP001 |
IA005 | R250 | R42 000 | RC001 |
IA006 | R250 | R42 000 | RC001 |
IA007 | R250 | R42 000 | AC001 |
IA008 | R250 | R42 000 | AC001 |
IA009 | R250 | R42 000 | AC001 |
IA010 | R250 | R42 000 | BD001 |
IA011 | R250 | R42 000 | AC001 |
IA012 | R250 | R42 000 | AP001 |
IA013 | R250 | R42 000 | DS001 |
IA014 | R250 | R42 000 | DS001 |
IA015 | R250 | R42 000 | OP001 |
IA016 | R250 | R42 000 | OP001 |
IA017 | R250 | R42 000 | AC001 |
IA018 | R250 | R42 000 | OP001 |
IA019 | R250 | R42 000 | TS001 |
IA020 | R250 | R42 000 | TS001 |
IA021 | R250 | R42 000 | TS001 |
IA022 | R250 | R42 000 | TS001 |
IA023 | R250 | R42 000 | TS001 |
IA024 | R250 | R42 000 | DS001 |
Department Projects with Hours
Project | Description | Department Code | Employee ID | Start Date | Start Time | End Date | End Time | Duration (h) | Hours |
SCREEN CV | SCREEN CV | AC001 | IA009 | 2022/01/12 | 13:00 | 2022/01/12 | 17:00 | 4:00 | 4:00 |
COLD CALLING / COMPANY | COLD CALLING / COMPANY | AC001 | IA009 | 2022/01/12 | 8:00 | 2022/01/12 | 12:00 | 4:00 | 4:00 |
SCREEN CV | SCREEN CV | AC001 | IA009 | 2022/01/11 | 13:00 | 2022/01/11 | 17:00 | 4:00 | 4:00 |
SCREEN CV | SCREEN CV | AC001 | IA009 | 2022/01/10 | 13:00 | 2022/01/10 | 17:00 | 4:00 | 4:00 |
COLD CALLING / COMPANY | COLD CALLING / COMPANY | AC001 | IA009 | 2022/01/11 | 8:00 | 2022/01/11 | 12:00 | 4:00 | 4:00 |
COLD CALLING / COMPANY | COLD CALLING / COMPANY | AC001 | IA009 | 2022/01/10 | 8:00 | 2022/01/10 | 12:00 | 4:00 | 4:00 |
POWER BI | POWER BI | DS001 | IA024 | 2022/01/12 | 13:00 | 2022/01/12 | 17:00 | 4:00 | 4:00 |
POWER BI | POWER BI | DS001 | IA024 | 2022/01/12 | 8:00 | 2022/01/12 | 12:00 | 4:00 | 4:00 |
POWER BI | POWER BI | DS001 | IA024 | 2022/01/11 | 9:00 | 2022/01/11 | 16:00 | 7:00 | 7:00 |
POWER BI | POWER BI | DS001 | IA024 | 2022/01/10 | 9:00 | 2022/01/10 | 16:00 | 7:00 | 7:00 |
INTERNAL IT | INTERNAL IT | DS001 | IA024 | 2022/01/10 | 8:00 | 2022/01/10 | 9:00 | 1:00 | 1:01 |
FN-0002 TRAINING | FN-0002 TRAINING | DS001 | IA014 | 2022/01/11 | 7:10 | 2022/01/11 | 16:00 | 8:50 | 8:51 |
FN-0002 TRAINING | FN-0002 TRAINING | DS001 | IA014 | 2022/01/10 | 9:00 | 2022/01/10 | 16:00 | 7:00 | 7:01 |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
118 | |
107 | |
70 | |
70 | |
43 |
User | Count |
---|---|
148 | |
104 | |
104 | |
89 | |
66 |