Trying to calculate productive score using three different summarized tables

[Sharma0815]

Hi Everyone,

I have three different summarized tables in my data model which represent user incoming calls handled, outbound calls, cases closed and I have one more table which is user login hours. 

I am trying to calculate the Productivity score of every user by using (calls handled + outbound calls + cases closed ) / Login Hours

Tables incoming calls handled, outbound calls, cases closed looks like below.

Calls Handled

user	calls_handled	User-work-day
UTYAA14	10	UTYAA14-2019-10-11
UTYAA15	6	UTYAA14-2019-10-12
UTYAA16	8	UTYAA14-2019-10-14
UTYAA17	9	UTYAA14-2019-10-15
UTYAA18	4	UTYAA14-2019-10-16

Outbound Calls

user	outbound_calls	User-work-day
UTYAA14	2	UTYAA14-2019-10-11
UTYAA15	4	UTYAA14-2019-10-12
UTYAA16	5	UTYAA14-2019-10-14
UTYAA17	1	UTYAA14-2019-10-15
UTYAA18	4	UTYAA14-2019-10-16

Cases_Closed

user	Cases_closed	User-work-day
UTYAA14	20	UTYAA14-2019-10-11
UTYAA15	11	UTYAA14-2019-10-12
UTYAA16	12	UTYAA14-2019-10-14
UTYAA17	7	UTYAA14-2019-10-15
UTYAA18	4	UTYAA14-2019-10-16

User login Hours

user	Login Hours	User-work-day
UTYAA12	8	UTYAA12 - 2019-10-11
UTYAA13	7	UTYAA13- 2019-10-12
UTYAA14	9	UTYAA14 - 2019-10-13
UTYAA15	9	UTYAA15 - 2019-10-14
UTYAA16	10	UTYAA16- 2019-10-15
UTYAA17	7	UTYAA17 - 2019-10-16

I've created a relationship between user login hours to all three tables using User-work-day and I tried to create a column  (calls handled + outbound calls + cases closed ) / Login Hours, but the columns are not showing up in the calculation.

Any help would be appreciated  

[CNENFRNL · Accepted solution]

Hi, @Sharma0815 , I recommend you create separate dimensional tables for dates and users; then create relationships among all tables, so that you can easily do some calculations. Here's the attached file  for more details.

[Sharma0815]

Both  solutions are giving correct result,Thanks @wdx223_Daniel , @CNENFRNL 

[wdx223_Daniel · Accepted solution]

Productivity Score :=
DIVIDE (
    CALCULATE (
        SUM ( 'Calls_Handled'[calls_handled] ),
        TREATAS ( VALUES ( User_login_Hours[user] ), Calls_Handled[user] )
    )
        + CALCULATE (
            SUM ( Outbound_Calls[outbound_calls] ),
            TREATAS ( VALUES ( User_login_Hours[user] ), Outbound_Calls[user] )
        )
        + CALCULATE (
            SUM ( Cases_Closed[Cases_closed] ),
            TREATAS ( VALUES ( User_login_Hours[user] ), Cases_Closed[user] )
        ),
    SUM ( User_login_Hours[Login Hours] )
)

[CNENFRNL · Accepted solution]

Hi, @Sharma0815 , I recommend you create separate dimensional tables for dates and users; then create relationships among all tables, so that you can easily do some calculations. Here's the attached file  for more details.