How to group this in Pivot Tables

[danhendo888]

https://1drv.ms/x/s!AvjBsEPEq12ngTpjIV0Ri5AcMKZa?e=0sU8cn

I have a table of data.
First column = company names.
Other columns = invoice amount aging by days
E.g. column B = 1 - 010 means invoices overdue between 1 day and 10 days.

I am summarizing the info in a PV.

Suppose I want to create a column showing invoices overdue between 1 day and 30 days.
I could either create a helper column on the table (adding columns b and c) or I could use Fields, Items, & Sets in the Pivot Table ribbon and create the column in the PV.

Which method would you guys recommend for this exercise? Is there a more solid method?

Thank you

 

[sandy666]

try with PowerQuery

[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    UnPivot = Table.UnpivotOtherColumns(Source, {"Customer Name"}, "Attribute", "Value"),
    IF = Table.AddColumn(UnPivot, "Invoices", each if [Attribute] = "1 - 010" then "1 - 30" else if [Attribute] = "011 - 030" then "1 - 30" else "031 - Above 180")
in
    IF[/SIZE]

then create PivotTable

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Sum of Value[/td][td=bgcolor:#DDEBF7]Invoices[/td][td=bgcolor:#DDEBF7][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Customer Name[/td][td=bgcolor:#DDEBF7]1 - 30[/td][td=bgcolor:#DDEBF7]031 - Above 180[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Company 1[/td][td]

122​

[/td][td]

304​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Company 2[/td][td]

25​

[/td][td]

404​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Company 3[/td][td]

104​

[/td][td]

225​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Company 4[/td][td]

52​

[/td][td]

285​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Company 5[/td][td]

194​

[/td][td]

390​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Grand Total[/td][td=bgcolor:#DDEBF7]

497​

[/td][td=bgcolor:#DDEBF7]

1608​

[/td][/tr]
[/table]

 

[danhendo888]

Beautiful. Thank you Sandy, I appreciate it

 

[sandy666]

you are welcome

Have a nice day