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 the data i am getting values similar to 2nd column(Billable_Utilization - As per my calculation)
But the correct value of Billable Utilization should be like  Column 6( Correct Billable_Utilization )

RequestorBillable_Utilization - As per my calculationBillable_Hours  Standard_Hr1  Leave_Hours  Correct Billable_Utilization 
(Billable_Hours /(Standard_Hr1 - Leave_Hours))*100
A22.97%36.75160 25.52%     (36.75 / (160-16))*100
B15.27%24.43160 16.96% (24.43 / (160-16))*100
C14.84%23.75160 16.49% (23.75/(160-16))*100
D10.34%16.55160 11.49 (16.55/(160-16))*100
Non-Billable  16016 

 

 

Below is the dax:

1) Billable_Utl = DIVIDE([Billable_Hours],[Standard_Hr1]-[Leave_Hours])

2)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"))
 
3) Standard_Hr1 = sum(vw_EHub_Fiscal_Calendar_Headcount_Tracker_Data[stdd_hr] )

4)
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"})
)

How to modify this dax so that i will get Leave_Hours in other rows (A, B, C, D). Here the problem is i need to populate the Leave_Hours in all rows.. the Leave_Hours (non-Billable = 16) are not being considered in the calculation.That's why iam getting wrong % in column 2.

Please note that "Standard_Hr1" is from a different table, but other values Billable_Hours , Leave_Hours are from the same table.

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.



12 Replies

    • WinterGarden's avatar
      WinterGarden
      Resolver I
      6 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
        Super User
        6 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

  • GaloyanTelman's avatar
    GaloyanTelman
    Frequent Visitor
    6 months ago

     

    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]
    ),
    ALLSELECTED ( vw_Timely_Ehub_EA_Work_Flow_Tracker_Data_User[Requestor] ),
    KEEPFILTERS ( vw_Timely_Ehub_EA_Work_Flow_Tracker_Data_User[Leave_Type]
        IN { "Holiday", "LeaveType" } )
)

     

    • WinterGarden's avatar
      WinterGarden
      Resolver I
      6 months ago

      Hi GaloyanTelman ,

      This one was giving correct billable utilization values..
      But was showing all the requestor names irrespective of the resource name.. Like requestors of other resources are also displaying in the matrix visual.