Divide values of two columns from different tables using group by

[thlndlmngls]

Hello.

I would like to know how to divide the values of two columns from two different tables using group by.

I made a table that used three data below and all the data come from the different tables. (There is no direct relatiopnships among those tables.)

-Table 1 [ID]

-Table 2 [Hours]

-Table 3 [Amount]

Now the calculated information below should be put in the table as well.

- Table 3 [Amount]/ Table 2 [Hours] group by Table 1 [ID]

I tried to create a new column to calculate above, but it is impossible as the data comes from the different tables and there is no direct relationship among the tables.

Should I create a new table to show this data or is it possible to use measure?

Would you tell me how to solve this problem?

Thank you in advance!

[Ashish_Mathur · Accepted solution]

Hi,

Assuming ID is the common column in both tables, create another Table with a single column which lists all unique ID's.  Build a relationship from the ID column of Table2 and Table3 to the ID column of this new Table.  To your visual, drag the ID field from this new table.  Write these measures

Total = SUM('Table3'[Amount])

Hours spent = SUM('Table2'[Hours])

Measure = [Total]/[Hours spent]

Hope this helps.

[thlndlmngls]

Hello Ashish,

Thank you for your reply.

It works well and the calculated data is put into the table.

Thank you so much again.

Regards

[Ashish_Mathur]

You are welcome.

[calerof]

Hi @thlndlmngls ,

I thought I replied to your question a minute ago. Have you made relationships between the tables yet?

Regards,

Fernando

[thlndlmngls]

Hello Fernando,

Thank you for your reply.

There was something went wrong so I have created the post again.

I have tried to use measure but cannot refer the columns from the tables.

>Have you made relationships between the tables yet?

Yes, but there is no direct relationship among tables.

Regards