Forum Discussion

Greenterer's avatar
Greenterer
Helper I
2 years ago
Solved

Using data from one table in a calculation in another table

I have two tables that look like this.  The tables are linked together by Name

Table 1

Name               Task                                  Date Completed      Year/Month

Joe                    Take the order                 April 5, 2023            202304

Joe                    Fill the order                    April 5, 2023            202304

Joe                     Fill the order                   April 6, 2023            202304

 

Table 2

Name                Year/Month                     Days Worked

Joe                    202304                             15

Joe                    202305                             21

 

The goal is to calculate how many tasks each person has completed per days worked each month.  I can  get a total for the tasks completed easily enough.  My problem comes when I try to bring in the days worked.  It seems like it should be simple (and I can do this in Tableau, but I've been forced to convert all of my Tableau workbooks into BI), but I can't find a way to divide the total tasks, which are calculated in Table 1, by the days worked from Table 2.  

           

Need Help
  • tzvetkov_b87's avatar
    tzvetkov_b87
    2 years ago

    Hi Greenterer ,

     

    The problem comes from having a many-many relationship and also not know what month you want to work out tasks per month.

     

    One way to fix the problem is to create a third table called "Users" which will contain just Joe's name. I am assuming there will be Bob, Alex and many more users.

     

    Then Users will have a 1-to-many relationship with Table 1 and Table 2 based on Name.

     

    Then you can add the following measure in Users:

    Average Tasks per day worked =
    VAR days_worked = SUM( 'Table 2'[Days Worked] )
    VAR total_tasks = COUNTA( 'Table 1'[Name] )

    RETURN
    DIVIDE( total_tasks, days_worked )
     
    If you just display this on a table with Joe's name and the measure it will give you 0.08.
     
    Joe's done 3 tasks in 2 months when he should have worked 36 days. 3/36 = 0.08
     
    If you then put the Year/Month field as well in the table it will show you what you want to see.
     

     

    Hope this helps.

     

    If this answer helped, please mark it as the correct one and a thumbs up would be great 🙂

     

    Boyan

2 Replies

  • tzvetkov_b87's avatar
    tzvetkov_b87
    Helper I
    2 years ago

    Hi Greenterer ,

     

    The problem comes from having a many-many relationship and also not know what month you want to work out tasks per month.

     

    One way to fix the problem is to create a third table called "Users" which will contain just Joe's name. I am assuming there will be Bob, Alex and many more users.

     

    Then Users will have a 1-to-many relationship with Table 1 and Table 2 based on Name.

     

    Then you can add the following measure in Users:

    Average Tasks per day worked =
    VAR days_worked = SUM( 'Table 2'[Days Worked] )
    VAR total_tasks = COUNTA( 'Table 1'[Name] )

    RETURN
    DIVIDE( total_tasks, days_worked )
     
    If you just display this on a table with Joe's name and the measure it will give you 0.08.
     
    Joe's done 3 tasks in 2 months when he should have worked 36 days. 3/36 = 0.08
     
    If you then put the Year/Month field as well in the table it will show you what you want to see.
     

     

    Hope this helps.

     

    If this answer helped, please mark it as the correct one and a thumbs up would be great 🙂

     

    Boyan