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: Group Date Demand A Jan 5 A Feb 4 ...
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] )
    )
)
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"