Calculate the different hours between two tables

[Yenn]

Hi @everyone,

I am trying to calculate the different hours between two tables but it's not working, what should I do in this case?

Table 2: 

Table 1: 

 When I typed the formula below, it shown: 

Hour comparison =

var _Table1 =SELECTEDVALUE(fHour[Hours])

var _Table2 =SELECTEDVALUE('fResults[Hour])

return

_Table2 - _Table1

[Yenn]

I already created the relationship "Many-to-Many" between two tables and I tried to using this formula but it didnt work. I want to describe in details two reports as the following:

+ Table 1 ( Hour report): each ID displayed the total hours for which operated everyday. Some IDs will repeat or they are added or removed depend on the business situation.

+ Table 2 ( Results): each ID displayed the total hours that customer spent everyday.

For example: On 19/01/2019, ID: BJ-01-01, Table 1 (Hour) = 21.37, Table 2 (Results) = 8.15

And the result I want to show:

[AmiraBedh]

Can you please share your pbix file ?

[AmiraBedh]

Are you considering the ID in your calculation ?

Logically, I can calculate the diffrence in hours between the two tables for the same ID.

In that case you need to join the two tables on the IDs so that you can perform calculations on the corresponding rows.You can then calculate the difference in hours between the two tables for the corresponding IDs.

Create Relationships Between Tablesi f you haven't already, you might want to create a relationship between the tables using the common ID field.

and then :

Difference_in_Hours =
SUMX(
    Table1, // Assuming you want to iterate through Table1
    VAR RelatedRow = RELATEDTABLE(Table2) // Fetch related row from Table2
    VAR Hours1 = Table1[hours] // Get hours from Table1
    VAR Hours2 = SUMX(RelatedRow, Table2[hours]) // Get hours from related row in Table2
    RETURN Hours1 - Hours2 // Return the difference
)