Forum Discussion
Matrix calculation Issue - DAX
krishnakanth240 ,
Hi ,
This is giving correct billable utilization values, but the problem is that other resources sponsors are also showing(The rows were Time_in_Hr and Billable_Utl columns are blank) since we removed the filters on the requestors..
Also the Non Billable_Utl should be blank for all requestors except Requestor = "Non- Billable" (it is showing 18.04% for all requestors..)
if i apply Time_In_Hr = is not blank on the filter pane of this matrix visual it will work.. But this Leave_Hour measure is causing issue in my another table viusual.. where all requestors are showing irrespective of the resource name..
So i created a duplicate of the table( vw_Timely_Ehub_EA_Work_Flow_Tracker_Data_User) where i filtered only the non billable rows(Billable column = Non-Billable) .. and updated the measures accordingly.. and it is working fine..Thank you so much for all the suggestions 🙂 I really appreciate it.
Hi krishnakanth240 ,
a) vw_EHub_Fiscal_Calendar_Headcount_Tracker_Data : stdd_hr is same for all the resource.. 40 hrs per week
b) (SponsorName, SponsorArea, SponsorRegion, SponsorCountry):
AJT, APAC, OCEANIA, Australia - for all rows
Hi WinterGarden
Thanks for sharing!
Want to clarify from which table these columns are coming from
| Requestor | Billable_Utilization - As per my calculation | Billable_Hours | Standard_Hr1 | Leave_Hours |
It's confusing actually. Can you give all the list of table names along with the columns and 30 sample records for it to work on DAX. Thank You!
Hi krishnakanth240 ,
There are two tables
1)vw_Timely_Ehub_EA_Work_Flow_Tracker_Data_User:FiscalWeek Resource Resource_GPN Requestor Billable Leave_Type Time_In_Hr SponsorName SponsorArea SponsorRegion SponsorCountry Week_GPN 20 PB PB01 A Billable 9.3 AJT APAC OCEANIA Australia 20PB01 21 PB PB01 A Billable 10.83 AJT APAC OCEANIA Australia 21PB01 22 PB PB01 A Billable 6.75 AJT APAC OCEANIA Australia 22PB01 23 PB PB01 A Billable 9.87 AJT APAC OCEANIA Australia 23PB01 20 PB PB01 B Billable 6.23 AJT APAC OCEANIA Australia 20PB01 21 PB PB01 B Billable 4.17 AJT APAC OCEANIA Australia 21PB01 22 PB PB01 B Billable 4.7 AJT APAC OCEANIA Australia 22PB01 23 PB PB01 B Billable 9.33 AJT APAC OCEANIA Australia 23PB01 20 PB PB01 C Billable 8.28 AJT APAC OCEANIA Australia 20PB01 21 PB PB01 C Billable 5.98 AJT APAC OCEANIA Australia 21PB01 22 PB PB01 C Billable 5.62 AJT APAC OCEANIA Australia 22PB01 23 PB PB01 C Billable 3.87 AJT APAC OCEANIA Australia 23PB01 20 PB PB01 D Billable 1.62 AJT APAC OCEANIA Australia 20PB01 21 PB PB01 D Billable 8.62 AJT APAC OCEANIA Australia 21PB01 22 PB PB01 D Billable 3.31 AJT APAC OCEANIA Australia 22PB01 23 PB PB01 D Billable 3 AJT APAC OCEANIA Australia 23PB01 20 PB PB01 Non-Billable Non-Billable NULL 0 AJT APAC OCEANIA Australia 20PB01 21 PB PB01 Non-Billable Non-Billable NULL 0 AJT APAC OCEANIA Australia 21PB01 22 PB PB01 Non-Billable Non-Billable LeaveType 16 AJT APAC OCEANIA Australia 22PB01 23 PB PB01 Non-Billable Non-Billable NULL 0 AJT APAC OCEANIA Australia 23PB01
2)vw_EHub_Fiscal_Calendar_Headcount_Tracker_DataFiscalWeek Resource stdd_hr Resource_GPN Week_GPN 20 PB 40 PB01 20PB01 21 PB 40 PB01 21PB01 22 PB 40 PB01 22PB01 23 PB 40 PB01 23PB01
# Requestor - is from Table 1(vw_Timely_Ehub_EA_Work_Flow_Tracker_Data_User)# Billable_Utilization - DIVIDE([Billable_Hours],[Standard_Hr1]-[Leave_Hours])
# Billable_Hours = CALCULATE(SUM(vw_Timely_Ehub_EA_Work_Flow_Tracker_Data_User[Time_In_Hr]),FILTER(vw_Timely_Ehub_EA_Work_Flow_Tracker_Data_User,vw_Timely_Ehub_EA_Work_Flow_Tracker_Data_User[Billable]="Billable"))
------Billable_Hours is from Table 1(vw_Timely_Ehub_EA_Work_Flow_Tracker_Data_User)-----# Standard_Hr1 = sum(vw_EHub_Fiscal_Calendar_Headcount_Tracker_Data[stdd_hr] )
----Standard_Hr1 is from Table 2(vw_EHub_Fiscal_Calendar_Headcount_Tracker_Data)-----
# Leave_Hours =CALCULATE(SUM(vw_Timely_Ehub_EA_Work_Flow_Tracker_Data_User[Time_In_Hr]),REMOVEFILTERS(vw_Timely_Ehub_EA_Work_Flow_Tracker_Data_User[SponsorName],vw_Timely_Ehub_EA_Work_Flow_Tracker_Data_User[SponsorArea],vw_Timely_Ehub_EA_Work_Flow_Tracker_Data_User[SponsorRegion],vw_Timely_Ehub_EA_Work_Flow_Tracker_Data_User[SponsorCountry]),KEEPFILTERS(vw_Timely_Ehub_EA_Work_Flow_Tracker_Data_User[Leave_Type] IN {"Holiday", "LeaveType"}))
---Leave_Hours is from Table 1(vw_Timely_Ehub_EA_Work_Flow_Tracker_Data_User)----
Also note that there is one - to - many relationship between Table 2 and Table 1 using "Week_GPN" columnHi WinterGarden
Thanks for sharing!
Leave hours measure I have tried with sample data. It is working after adding Requestor column. Can you please try and confirm
krishnakanth240 ,
Hi ,
This is giving correct billable utilization values, but the problem is that other resources sponsors are also showing(The rows were Time_in_Hr and Billable_Utl columns are blank) since we removed the filters on the requestors..
Also the Non Billable_Utl should be blank for all requestors except Requestor = "Non- Billable" (it is showing 18.04% for all requestors..)
if i apply Time_In_Hr = is not blank on the filter pane of this matrix visual it will work.. But this Leave_Hour measure is causing issue in my another table viusual.. where all requestors are showing irrespective of the resource name..
So i created a duplicate of the table( vw_Timely_Ehub_EA_Work_Flow_Tracker_Data_User) where i filtered only the non billable rows(Billable column = Non-Billable) .. and updated the measures accordingly.. and it is working fine..Thank you so much for all the suggestions 🙂 I really appreciate it.