Problem with showing multiplying hours per pay rate.

[Anonymous]

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

[v-yalanwu-msft · Accepted 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.

[amitchandak]

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

Hi amitchandak,

Thanks for the reply.

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

[amitchandak]

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

[Anonymous]

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