Forum Discussion

WinterGarden's avatar
WinterGarden
Icon for Resolver I rankResolver I
7 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
    7 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.



krishnakanth240's avatar
krishnakanth240
Icon for Super User rankSuper User
7 months ago

Hi WinterGarden 

 

Can you provide the sample data of columns with the tables to work on the above DAX measures to check. Thank you!

WinterGarden's avatar
WinterGarden
Icon for Resolver I rankResolver I
7 months ago

Hi krishnakanth240 ,
This is the sample data, please note that i have two filters FiscalWeek and resource name..

FiscalWeekResourceRequestorBillableLeave_TypeTime_In_Hr
20PBABillable 9.3
21PBABillable 10.83
22PBABillable 6.75
23PBABillable 9.87
20PBBBillable 6.23
21PBBBillable 4.17
22PBBBillable 4.7
23PBBBillable 9.33
20PBCBillable 8.28
21PBCBillable 5.98
22PBCBillable 5.62
23PBCBillable 3.87
20PBDBillable 1.62
21PBDBillable 8.62
22PBDBillable 3.31
23PBDBillable 3
20PBNon-BillableNon-BillableNULL0
21PBNon-BillableNon-BillableNULL0
22PBNon-BillableNon-BillableLeaveType16
23PBNon-BillableNon-BillableNULL0



Standard_Hr1 is from a different table(vw_EHub_Fiscal_Calendar_Headcount_Tracker_Data), that has many to many relationship with the table: vw_Timely_Ehub_EA_Work_Flow_Tracker_Data_User


Below is the sample data for standard hour:

FiscalWeekResourcestdd_hr
20PB40
21PB40
22PB40
23PB40
  • krishnakanth240's avatar
    krishnakanth240
    Icon for Super User rankSuper User
    7 months ago

    Hi WinterGarden 

    Thanks for sharing for one table.

    Could you please share these inputs.

    a)Some more data of records for these columns(Fiscal Week, Resource, stdd_hr) for 'Table' - vw_EHub_Fiscal_Calendar_Headcount_Tracker_Data

     

    b)Sample data of rows for these columns(SponsorName, SponsorArea, SponsorRegion, SponsorCountry) with records for the above 'Table' you shared - vw_Timely_Ehub_EA_Work_Flow_Tracker_Data_User

    • WinterGarden's avatar
      WinterGarden
      Icon for Resolver I rankResolver I
      7 months ago

      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

      • krishnakanth240's avatar
        krishnakanth240
        Icon for Super User rankSuper User
        7 months ago

        Hi WinterGarden 

         

        Thanks for sharing!

         

        Want to clarify from which table these columns are coming from 

        RequestorBillable_Utilization - As per my calculationBillable_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!