Dax Formula with 1:M Relationship

[Anonymous]

Hi Everyone,

How can I find the %schedule out of capacity for each month?

I have two tables. 

Table1 contains Unique Roles and respective Capacities in each month.

TB1:

ROLE	FEB-CAPACITY	MAR-CAPACITY	APR-CAPACITY	MAY-CAPACITY
A	150	172.5	157.5	165
C	172.5	150	165	172.5
E	157.5	165	172.5	150
F	165	157.5	150	157.5

Table2 contains Roles, EMP_ID, Project Label (Billable,Holiday..etc)  and their respective Schedule in each month. 

TB2:

ROLE	EMP_ID	Project Label	FEB-SCHEDULE	MAR-SCHEDULE	APR-SCHEDULE	MAY-SCHEDULE
A	1	Billable	135	172.5	157.5	150
F	3	Billable	0	86.25	150	165
C	8	Billable	30	37.5	0	45
A	14	Holiday	45	52.5	7.5	0
E	26	Billable	80	125	64	78
F	47	Holiday	65	105	97	0
C	87	Billable	35	37.5	48	98
F	92	Billable	59	0	65	110
E	98	Billable	25	45	70	85

Based on the  Roles in the both tables I created One to Many relationship.

I can write Dax to findout %schedule out of capacity for each month when I select Single role by using Slicer.

%Schedule FEB= DIVIDE(SUM(TB2[FEB-SCHEDULE]), SUM(TB1[FEB-CAPACITY])*COUNT(TB2[ROLE]), 0).

This formula is not working when I select multiple roles/all the Roles.

-How can I write the dax formula to findout %schedule out of capacity for each month for all roles?

I got the answer by using Merge Operation & Lookupvalue. But, Is there any way to write DAX formula to findout the answer while using One to Many Relationship?

 Thanks in Advance.

[CNENFRNL]

@Anonymous , simply unpivotting your dataset might render the scanrio way much easier,

[Anonymous]

Hi CNENFRNL,

Thanks for your reply. But the solution was wrong,

If you see For role A in month May,

Total Schedule: 150+0= 150

Total Capacity: 165+165=330(Because we have two EMP_ID with same Role A in month May)

%Schedule of A in May :150/330= 45.45%(Actual Answer).

Hope you understand my point.

Thank You.

[CNENFRNL]

@Anonymous , so you might want to tweak the measure this way

% schedule out of capacity = 
DIVIDE (
    CALCULATE ( SUM ( SCHEDULE[SCHEDULE] ) ),
    CALCULATE ( SUM ( CAPACITY[CAPACITY] ) ) * DISTINCTCOUNT( SCHEDULE[EMP_ID] )
)

[Anonymous]

@CNENFRNL , That's what  exactly I did and also mentioned it in my question.

%Schedule FEB= DIVIDE(SUM(TB2[FEB-SCHEDULE]), SUM(TB1[FEB-CAPACITY])*COUNT(TB2[ROLE]), 0).

But this formula is not working for %schedule out of capacity for each month for all roles. 

% schedule out of capacity for May for all Roles=Total Schedule in May/Total Capacity in May

                                                                            =731/1447.5

                                                                            = 50.50% ( Actual Answer)

You can see, in your formula % schedule for May for all Roles = 731/(645*9)

                                                                                                    =12.59%( wrong Answer)

[CNENFRNL · Accepted solution]

@Anonymous , I hope this is what your expect,

% schedule out of capacity = 
DIVIDE (
    CALCULATE ( SUM ( SCHEDULE[SCHEDULE] ) ),
    SUMX (
        ALLSELECTED ( dMONTH[MTH] ),
        SUMX (
            DISTINCT ( dRole[ROLE] ),
            CALCULATE ( SUM ( CAPACITY[CAPACITY] ) * DISTINCTCOUNT ( SCHEDULE[EMP_ID] ) )
        )
    )
)