LOOKUPVALUE the right DAX?

[ITManuel]

Hi there,

i would like to analyze project and department related hours and costs from a data set from my ERP system. I have loaded two tables into my PBI file.

Table 1:

Project   Department    Employee    Date    Work process     Hours 

     X               X                     X             X                X                   X   

Table 2:

Department      Hourly rate

      X                        X

I created a Matrix which shows the sum of hours for each project and department. Now I would like to calculate the costs for each department taking into consideration the hourly rates contained in table 2. 

I tried the LOKKUPVALUE DAX as below, but can only use static search values such as "Engineering" for example. 

HR = LOOKUPVALUE(Departments[Hourly rate];Departments[Department Name];"Engineering")

I would need variable search values to identify the hourly rates for each department and multiply it with the sum of hours in the matrix.

Can somebody help please?

Thanks

[Anonymous · Accepted solution]

// This is the simplest solution
// but it can be slow if HourLedger
// is big because RELATED is
// doing a context transition for
// each and every row it operates
// on.

// There's a relationship
// Department[Department] 1:* HourLedger[Department].
// One-way filtering from the dimension to
// the fact table.

[Amount] =
	SUMX(
		HourLedger,
		HourLedger[Hours] * RELATED( Department[HourlyRate] )
	)
	
// This one will be more performant

[Total Hours] = SUM( HourLedger[Hours] )

[Amount] =
	SUMX(
		Department,
		[Total Hours] * Department[HourlyRate]
	)
	
// Bear in mind, though, that the column Department in HourLedger
// should be hidden and no slicing by it should take
// place. All slicing is always done through dimensions,
// never directly on a fact table.

Best

D

[ITManuel]

Hi darlove,

both ways working perfectly. 

Thank you very much, great help. 👍

[Kogika]

If you create a relationship One to Many on department, you can use the Related function within your SUMX expression to get the desired results.

[ITManuel]

Thanks for the quick responses.

Both tables are already related, but cannot solve it. I tried HR = sumx(Departments;RELATED(  .....it will not propose any columns here)

I have multiple entries for each department in table 2, for example 3 for Engineering department. Will the sumx not result in 3 time the hourly rate of that department?

Thanks

[camargos88]

@ITManuel ,

Can you provide some sample data ?

Ricardo

[ITManuel]

Sorry I'm unable to upload files. How does it works?

[camargos88]

@ITManuel ,

You can paste the data here or upload the pbix using onedrive, googledrive, dropbox..

Ricardo

[ITManuel]

Can't past it here for some reason.

Here is a download link. 

https://we.tl/t-J8LOOh4gDv

Thanks

[camargos88]

@ITManuel ,

Try relating both tables and SUMX function.

So you can multiply the qty hours by hour rate in a measure.

Ricardo