Forum Discussion

Liz McNeil's avatar
Liz McNeil
Copper Contributor
Nov 15, 2017

Calculate Resource Totals Across Time Period

Not an Excel power user so I could use some help : )     I have a spreadsheet that shows resource allocation by project, but I want to have a view where I can see totals for each week for each res...
Charting
excel
Formulas and Functions
office 365
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Nov 16, 2017

Hi Liz,

 

Both works. With formulas you shall create another table with unique names there you will collect summaries. With PivotTable unique names will be generated automatically, all weeks are to be added to calculations of summary.

 

Perhaps the easiest way is to use Power Query (aka Get&Transform), that is few steps script which returns you the summary table, for that sample

the script generated from user interface is

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {"Project", "Name"}, "Attribute", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Columns", {"Name", "Attribute"}, {{"Totals", each List.Sum([Value]), type number}}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Grouped Rows", {{"Attribute", type text}}, "en-GB"), List.Distinct(Table.TransformColumnTypes(#"Grouped Rows", {{"Attribute", type text}}, "en-GB")[Attribute]), "Attribute", "Totals", List.Sum)
in
    #"Pivoted Column"

and in attached file.

Resources