cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
WimBi
Frequent Visitor

Calculate hours worked with category rate from other table

Hi!


I have a question about DAX. I’m trying to calculate the costs of the total hours worked in one table. The problem is the rate category is in a other table (see example data). I’m using DirectQuery mode, so calculated columns are a bit harder to use.


So far I got this far, but I need the “0001” to be variable, because different people work for different ‘categories’.

 

 

 

CALCULATE(SUM(urenverantwoording[Hours Worked]),urenverantwoording[Category]="0001") * CALCULATE(SUM(m_urensoorten[Rate]),urenverantwoording[Category]="0001")

 

 

Urenverantwoording = table 1
m_urensoorten = table 2

Employee (1)Hours worked (1)Category (1) id (2)Category (2) Rate (2)
JaBe0,51 412 null 
ErAa11 61 €    45,00
JaBe0,251 1081 €           -  
ErAa0,511 1291 €           -  
JaBe4,0112 1511 €    25,00
mont60,512 16u1 €           -  
ErAa0,2512 17x33 €           -  
ErAa0,2512 1813 €    40,00
ErAa0,2512 1991 €           -  
JaBe0,2511    
JaBe0,251    
JaBe0,251    
ErAa0,251    
ErAa0,2512    
JaMo091    

Can someone please help me? If I need to provide more information, please let me know!


Thanks in advance – Willem

 

1 ACCEPTED SOLUTION
johnt75
Super User
Super User

Set up a one-to-many relationship from table 2 to table 1, then you can use

Total value =
SUMX (
    Urenverantwoording,
    Urenverantwoording[Hours worked] * RELATED ( m_urensoorten[Rate] )
)

View solution in original post

2 REPLIES 2
johnt75
Super User
Super User

Set up a one-to-many relationship from table 2 to table 1, then you can use

Total value =
SUMX (
    Urenverantwoording,
    Urenverantwoording[Hours worked] * RELATED ( m_urensoorten[Rate] )
)
WimBi
Frequent Visitor

Thank you! I also already found a other way, but this also works great!

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors