Forum Discussion

WinterGarden's avatar
WinterGarden
Resolver I
6 months ago
Solved

Matrix calculation Issue - DAX

Hi All, I have a matrix visual similar to below table, where i have to show "Billable_Utilization"(2nd Column) Billable_Utilization = (Billable_Hours / (Standard_Hr1 - Leave_Hours))*100 As per...
Need Help
  • WinterGarden's avatar
    WinterGarden
    6 months ago

    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.



WinterGarden's avatar
WinterGarden
Resolver I
6 months ago

Hi krishnakanth240 ,

There are two tables
1)vw_Timely_Ehub_EA_Work_Flow_Tracker_Data_User:

 FiscalWeek  Resource  Resource_GPN  Requestor  BillableLeave_Type Time_In_Hr  SponsorName   SponsorArea   SponsorRegion   SponsorCountry   Week_GPN  
20PBPB01ABillable 9.3AJTAPACOCEANIAAustralia20PB01
21PBPB01ABillable 10.83AJTAPACOCEANIAAustralia21PB01
22PBPB01ABillable 6.75AJTAPACOCEANIAAustralia22PB01
23PBPB01ABillable 9.87AJTAPACOCEANIAAustralia23PB01
20PBPB01BBillable 6.23AJTAPACOCEANIAAustralia20PB01
21PBPB01BBillable 4.17AJTAPACOCEANIAAustralia21PB01
22PBPB01BBillable 4.7AJTAPACOCEANIAAustralia22PB01
23PBPB01BBillable 9.33AJTAPACOCEANIAAustralia23PB01
20PBPB01CBillable 8.28AJTAPACOCEANIAAustralia20PB01
21PBPB01CBillable 5.98AJTAPACOCEANIAAustralia21PB01
22PBPB01CBillable 5.62AJTAPACOCEANIAAustralia22PB01
23PBPB01CBillable 3.87AJTAPACOCEANIAAustralia23PB01
20PBPB01DBillable 1.62AJTAPACOCEANIAAustralia20PB01
21PBPB01DBillable 8.62AJTAPACOCEANIAAustralia21PB01
22PBPB01DBillable 3.31AJTAPACOCEANIAAustralia22PB01
23PBPB01DBillable 3AJTAPACOCEANIAAustralia23PB01
20PBPB01Non-BillableNon-BillableNULL0AJTAPACOCEANIAAustralia20PB01
21PBPB01Non-BillableNon-BillableNULL0AJTAPACOCEANIAAustralia21PB01
22PBPB01Non-BillableNon-BillableLeaveType16AJTAPACOCEANIAAustralia22PB01
23PBPB01Non-BillableNon-BillableNULL0AJTAPACOCEANIAAustralia23PB01


2)vw_EHub_Fiscal_Calendar_Headcount_Tracker_Data

 FiscalWeek  Resource  stdd_hr  Resource_GPN  Week_GPN 
20PB40PB0120PB01
21PB40PB0121PB01
22PB40PB0122PB01
23PB40PB0123PB01


# 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" column

krishnakanth240's avatar
krishnakanth240
Super User
6 months ago

Hi 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

 

  • WinterGarden's avatar
    WinterGarden
    Resolver I
    6 months ago

    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.



  • v-achippa's avatar
    v-achippa
    Community Support
    6 months ago

    Hi WinterGarden,

     

    Thank you krishnakanth240 and GaloyanTelman for the prompt response. 

     

    Thank you for confirming that the issue is resolved now. Thank you for being part of Microsoft Fabric Community.

     

    Thanks and regards,

    Anjan Kumar Chippa

  • krishnakanth240's avatar
    krishnakanth240
    Super User
    6 months ago

    That's great to hear WinterGarden it got solved.

     

    If the approach has helped to meet requirement, please give a heads-up/accept as a solution. Thank you!