Forum Discussion

Rhndy's avatar
Rhndy
Frequent Visitor
8 years ago
Solved

Need help calculating from three tables

 

Hi guys,

 

I'm sorry for the somewhat strange title. I don't know how to call the issue that i have.

Let me explain, i have 3 tables which look like this:

Table 1 - M

Columns:

Date/time of creation

Record number

 

Table 2 - A

Columns:

Date/time of creation

Record number

 

Table 3 - F

Columns:

Month (e.g. March 2018)

Number of employees

 

What do i want to achieve

I already have calculated the number of creations per month in two ways:

Via a calculated column:

 

MonthCreation = MONTH(Date/Time of Creation)

 

When i put the above in a bar chart where MonthCreation is on the Axis and the Count of Record numbers is the value. It works.

But i want the number of employees as a line plotted as well in the same graph. My end-game here is dividing the total number of records (from table M & F) against the number of employees. Example: March2018 created 2000 records and i have 2 employees, which results in 1000 records per employee in March.

 

Can you guys help me out in how i can achieve this?

 

 

  • Washivale's avatar
    Washivale
    8 years ago

    Hi,

    Create a column in Table M and A as follow:

     

    Month Name = FORMAT([Date/Time of Creation], "MMM") & " " & Year([Date/Time of Creation])

     

    to ensure you have similar formats for Month in all three tables

     

    then create a relationship between all three tables using Month column.

     

    once you created relationship you should create following measure

     

    Records/Employees = Divide((Count('table 1'[Record Number])+Count('table 2'[Record Number])),Sum('table 3'[Number of employees],0)

     

    let me know if it works.

     

    Thank you

2 Replies

  • Washivale's avatar
    Washivale
    Resolver V
    8 years ago

    Hi,

    Create a column in Table M and A as follow:

     

    Month Name = FORMAT([Date/Time of Creation], "MMM") & " " & Year([Date/Time of Creation])

     

    to ensure you have similar formats for Month in all three tables

     

    then create a relationship between all three tables using Month column.

     

    once you created relationship you should create following measure

     

    Records/Employees = Divide((Count('table 1'[Record Number])+Count('table 2'[Record Number])),Sum('table 3'[Number of employees],0)

     

    let me know if it works.

     

    Thank you

    • Rhndy's avatar
      Rhndy
      Frequent Visitor
      8 years ago

      Hi,

       

      Thanks for your help. it works!

      You are a hero :)