Need help calculating from three tables

[Rhndy]

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 · Accepted solution]

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]

Hi,

Thanks for your help. it works!

You are a hero 🙂