Forum Discussion

dsl72's avatar
dsl72
Regular Visitor
8 years ago
Solved

Calculating utilization by month

I have one set of data with demand by group by month, and another showing capacity of each group by month (see examples of data below):

 

Demand:

GroupDateDemand
AJan5
AFeb4
AMar6
AJan10
AFeb12
AMar15
BJan2
BFeb2
BMar3
BJan8
BFeb6
BMar5
CJan4
CFeb3
CMar7
CJan7
CFeb5
CMar4

 

Capacity:

GroupJanFebMar
A151520
B121010

 

I would like to summarize the demand by group by month and divide the results by capacity by month, resulting in a utilization pct by month, which would look something like this:

GroupJanFebMar
A100%107%105%
B83%80%80%

 

I've tried several approaches with different calculations and data relationships (including an attempt at a bridge table), but haven't gotten anything to work.  I'm obviously missing some basic concept.  Any suggestions?

Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    dsl72

     

    One of the methods

     

    Go to Modelling Tab>>>NEW TABLE and enter this formula

     

    Table =
SUMMARIZE (
    Capacity,
    Capacity[Group],
    "Jan", DIVIDE (
        CALCULATE ( SUM ( Demand[Demand] ), Demand[Date] = "Jan" ),
        SUM ( Capacity[Jan] )
    ),
    "Feb", DIVIDE (
        CALCULATE ( SUM ( Demand[Demand] ), Demand[Date] = "Feb" ),
        SUM ( Capacity[Feb] )
    ),
    "Mar", DIVIDE (
        CALCULATE ( SUM ( Demand[Demand] ), Demand[Date] = "Mar" ),
        SUM ( Capacity[Mar] )
    )
)

6 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    dsl72

     

    One of the methods

     

    Go to Modelling Tab>>>NEW TABLE and enter this formula

     

    Table =
SUMMARIZE (
    Capacity,
    Capacity[Group],
    "Jan", DIVIDE (
        CALCULATE ( SUM ( Demand[Demand] ), Demand[Date] = "Jan" ),
        SUM ( Capacity[Jan] )
    ),
    "Feb", DIVIDE (
        CALCULATE ( SUM ( Demand[Demand] ), Demand[Date] = "Feb" ),
        SUM ( Capacity[Feb] )
    ),
    "Mar", DIVIDE (
        CALCULATE ( SUM ( Demand[Demand] ), Demand[Date] = "Mar" ),
        SUM ( Capacity[Mar] )
    )
)
      • dsl72's avatar
        dsl72
        Regular Visitor
        8 years ago

        Thank you -- that worked great!

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Dear Zubair,

       

      Thanks for your help. That way, I have calculated utilization by personal. But I also need to utilize by departmant. In company, we are using Timesheet Application. We are taking their datas from sharepoint. So each week new datas are coming from personnal. Here I have a Calculated Table. In this table, I am measuring utilization by personnal with your solution. These are utilization of the personnal by week. However, I also need to measure them by Department. Each week measuring by itself. So it is empty, if that week didn't come.

       

      PersonalDepartmentJobWeek 1Week 2Week 3Week 4…Week 53
      AResearchEngineer0.990.450.380.44  
      BResearchTechnician0.730.360.710.36  
      CSalesService0.340.710.410.61  
      DResearchEngineer0.460.560.390.57  
      EResearchEngineer1.230.370.940.89  


      DAX is like that;


      Table =
      SUMMARIZE (
      RateListBU;
      RateListBU[Department];RateListBU[Job];RateListBU[Personal];
      "Week 1"; DIVIDE (
      CALCULATE (SUM ( TimeSheetPlusListBU[Billable Hours] ); TimeSheetPlusListBU[Period] = "Week 1");
      SUM ( RateListBU[Week 1] )
      );
      "Week 2"; DIVIDE (
      CALCULATE (SUM ( TimeSheetPlusListBU[Billable Hours] ); TimeSheetPlusListBU[Period] = "Week 2");
      SUM ( RateListBU[Week 2] )
      );
      "Week 3"; DIVIDE (
      CALCULATE (SUM ( TimeSheetPlusListBU[Billable Hours] ); TimeSheetPlusListBU[Period] = "Week 3" );
      SUM ( RateListBU[Week 3] )
      );
      "Week 4"; DIVIDE (
      CALCULATE (SUM ( TimeSheetPlusListBU[Billable Hours] ); TimeSheetPlusListBU[Period] = "Week 4" );
      SUM ( RateListBU[Week 4] )
      );
      .

      .

      "Week 53"; DIVIDE (
      CALCULATE (SUM ( TimeSheetPlusListBU[Billable Hours] ); TimeSheetPlusListBU[Period] = "Week 53" );
      SUM ( RateListBU[Week 53] )
      );
      )

      With each week, new week utilization value by personal is creating. How can I calculate utilize by departmant? For example Week 1 of Research Department Utilization?

  • lndnbrg's avatar
    lndnbrg
    Resolver III
    8 years ago
    Hi!

    I would unpivot the capacity table in the query, so that you get a month column. Now, you have the same columns like the demand table.

    Now, I would make sure that you have tables with groups and months. You can now create relationships to both tables.

    In the end, you can create a simple measure: DIVIDE(SUM(Demand[Demand]);SUM(Capacity[Capacity]))

    Does this make sense for you?
  • horseyride's avatar
    horseyride
    Frequent Visitor
    8 years ago

    Bring both tables into powerquery

    For capacity, Unpivot the table to get each month in its own row

    For demand, group on Group/Date to combine the demand numbers

    Then Merge in Capacity and expand, adding each capacity number

    In a pivot table based on Demand Query add a calculated field with formulat =IFERROR(Sum/Capacity,NA())

    Voila

     

     

    Table Query

    -----------------

    let
        Source = Excel.CurrentWorkbook(){[Name="Capacity"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Group", type text}, {"Jan", Int64.Type}, {"Feb", Int64.Type}, {"Mar", Int64.Type}}),
        #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Group"}, "Attribute", "Value"),
        #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Date"}})
    in
        #"Renamed Columns"

     

    Demand Query

    ------------

    let
        Source = Excel.CurrentWorkbook(){[Name="Demand"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Group", type text}, {"Date", type text}, {"Demand", Int64.Type}}),
        #"Grouped Rows" = Table.Group(#"Changed Type", {"Group", "Date"}, {{"Sum", each List.Sum([Demand]), type number}}),
        #"Merged Queries" = Table.NestedJoin(#"Grouped Rows",{"Group", "Date"},Capacity,{"Group", "Date"},"NewColumn",JoinKind.LeftOuter),
        #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"Value"}, {"Capacity"})
    in
        #"Expanded NewColumn"