Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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] ) )
)
)
)
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
@Anonymous , simply unpivotting your dataset might render the scanrio way much easier,
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
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.
@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] )
)
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
@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)
@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] ) )
)
)
)
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
26 | |
20 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
43 | |
19 | |
18 |