Calculated field in Pivot Table

[niam07]

Hi ya,

I have been searching all through the forum but cannot get my head around Pivot Table calculated field, will be very appreciate of any help. I have raw data as following. I buy stock from different suppliers. Every item has a code. If I buy same stock from different suppliers, my code remains same. My category determines whether I am buying or selling stock.

Suppliers or client	Code	Category	Item	Quantity
Supplier 1	1001	Purchase	Sofa	15
Supplier 1	1002	Purchase	Bed	30
Client 1	1001	Sell	Sofa	1
Supplier 2	1001	Purchase	Sofa	5
Client 2	1001	Sell	Sofa	3
Client 3	1002	Sell	Bed	1

<tbody>
</tbody>

I need Pivot table summary as following where it gives me the balance of my remaining stock.

	Code	Sum of Purchase Quantity	Stock Remaining
Sofa	1001	20	16
Bed	1002	30	29

<tbody>
</tbody>

Thanks a lot.

Naim

 

[sandy666]

You can try with PowerQuery aka Get&Transform:

Suppliers or client	Code	Category	Item	Quantity		Item	Code	Purchase	OnStock
Supplier 1	1001​	Purchase	Sofa	15​		Sofa	1001​	20​	16​
Supplier 1	1002​	Purchase	Bed	30​		Bed	1002​	30​	29​
Client 1	1001​	Sell	Sofa	1​
Supplier 2	1001​	Purchase	Sofa	5​
Client 2	1001​	Sell	Sofa	3​
Client 3	1002​	Sell	Bed	1​

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Pivoted Column" = Table.Pivot(Source, List.Distinct(Source[Category]), "Category", "Quantity"),
    #"Grouped Rows" = Table.Group(#"Pivoted Column", {"Code"}, {{"Count", each _, type table}, {"Purchase", each List.Sum([Purchase]), type number}, {"Sell", each List.Sum([Sell]), type number}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "OnStock", each [Purchase]-[Sell]),
    #"Added Custom" = Table.AddColumn(#"Added Custom1", "Item", each List.Distinct(Table.Column([Count],"Item"))),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Item", each Text.Combine(List.Transform(_, Text.From)), type text}),
    #"Reordered Columns" = Table.ReorderColumns(#"Extracted Values",{"Item", "Code", "Count", "Purchase", "Sell"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Count", "Sell"})
in
    #"Removed Columns"[/SIZE]

 

[niam07]

Hi Sandy,

Thanks a lot. Sorry for being dummy but where do I enter the code that you have provided. I am using office 365 and Googled but cannot find where to input this code. if you could provide me bit detailed info on how to input this code please.

Thanks,
Niam

 

[niam07]

Hi Sandy,

Thanks a lot. Sorry for being dummy but where do I enter the code that you have provided. I am using office 365 and Googled but cannot find where to input this code. if you could provide me bit detailed info on how to input this code please.

Thanks,
Niam

 

[sandy666]

- Create Table from your source table (Ctrl+T) Make sure the name of this table is Table1 (Formulas - Name Manager)
- Click inside the table
- Data - From table

you will see PowerQuery editor

go to Home - Advanced Editor

- delete all what you see there then paste code from the post
- Close & Load - you should see new sheet with result table
-
here is example file

 

[Fazza]

Another option, which AFAIK works for any Excel version with pivot tables. So since the mid 90s?

Give the source data a simple (non-dynamic) defined name. say YourTableName then save the file. From a new file (CTRL-N), start the pivot table wizard (ALT-D-P) and choose external data source, next, get data, Excel files, OK, browse for the file, OK, see the defined name, select a field or more, then follow wizard to the end choosing the option to edit in MS Query at the last step. Via the SQL button change the text to

SELECT Item, Code, IIF(Category='Purchase',1,0)*Quantity AS [Purchase Quantity], IIF(Category='Purchase',1,-1)*Quantity AS [Stock Remaining]
FROM YourTableName

OK to enter, OK to acknowledge message about not being able to graphically represent, then via the open door icon exit MS Query & make the pivot table. Once created, the worksheet containing the pivot table can be moved into the source workbook if you like.

If you want other fields in the pivot table, then add them in the above definition.

If you want the SUM directly in the dataset without the details, then use

SELECT Item, Code, SUM(IIF(Category='Purchase',1,0)*Quantity) AS [Purchase Quantity], SUM(IIF(Category='Purchase',1,-1)*Quantity) AS [Stock Remaining]
FROM YourTableName
GROUP BY Item, Code

And, instead of a pivot table this could be just a query.

 

[niam07]

Hi Sandy,

Thanks for this. I found it easy to download your file and make changes there. It worked for some values but for some reason as my raw data increased, i.e. as my codes increased in my table after 1006, the quantity part is not showing.

I have posted my raw data and desired query as follows. Will really appreciate your input into this. For some reason, output does not does not add up all quantities. Thanks a lot.

Raw Data

Category	Item	Code	Suppliers or client	Quantity
Purchase	Sofas	1001	Supplier 1	60
Purchase	Tea Table	1003	Supplier 2	3
Sales	Sofas	1001	Supplier 1	2
Purchase	Mattress	1004	Supplier 3	2
Purchase	Mattress	1004	Supplier 4	2
Purchase	Bed	1002	Supplier 2	1
Purchase	Bed	1002	Supplier 2	1
Sales	Bed	1002	Supplier 2	1
Purchase	Sofas	1001	Supplier 1	1
Sales	Sofas	1001	Supplier 1	1
Purchase	Sofas	1001	Supplier 1	4
Purchase	Dinning Table	1005	Supplier 5	5
Sales	Dinning Table	1005	Supplier 5	2
Purchase	Divan	1006	Supplier 4	2
Purchase	Sofas	1001	Supplier 6	4
Purchase	Office Chair	1007	Supplier 6	5
Sales	Office Chair	1007	Supplier 6	5

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

Required Output Item Code Purchase Sell OnStock Bed 1002 Dinning Table 1005 5 Divan 1006 2 Mattress 1004 4 Office Chair 1007 5 Sofas 1001 Tea Table 1003 3 <colgroup><col><col><col><col><col></colgroup><tbody> </tbody>

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[sandy666]

With your new example:

Category	Item	Code	Suppliers or client	Quantity		Item	Code	Purchase	OnStock
Purchase	Sofas	1001​	Supplier 1	60​		Bed	1002​	2​	1​
Purchase	Tea Table	1003​	Supplier 2	3​		Dinning Table	1005​	5​	3​
Sales	Sofas	1001​	Supplier 1	2​		Divan	1006​	2​
Purchase	Mattress	1004​	Supplier 3	2​		Mattress	1004​	4​
Purchase	Mattress	1004​	Supplier 4	2​		Office Chair	1007​	5​	0​
Purchase	Bed	1002​	Supplier 2	1​		Sofas	1001​	69​	66​
Purchase	Bed	1002​	Supplier 2	1​		Tea Table	1003​	3​
Sales	Bed	1002​	Supplier 2	1​
Purchase	Sofas	1001​	Supplier 1	1​
Sales	Sofas	1001​	Supplier 1	1​
Purchase	Sofas	1001​	Supplier 1	4​
Purchase	Dinning Table	1005​	Supplier 5	5​
Sales	Dinning Table	1005​	Supplier 5	2​
Purchase	Divan	1006​	Supplier 4	2​
Purchase	Sofas	1001​	Supplier 6	4​
Purchase	Office Chair	1007​	Supplier 6	5​
Sales	Office Chair	1007​	Supplier 6	5​

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Pivoted Column" = Table.Pivot(Source, List.Distinct(Source[Category]), "Category", "Quantity", List.Sum),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Suppliers or client"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Code"}, {{"Count", each _, type table}, {"Purchase", each List.Sum([Purchase]), type number}, {"Sales", each List.Sum([Sales]), type number}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "OnStock", each [Purchase]-[Sales]),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Sales"}),
    #"Added Custom1" = Table.AddColumn(#"Removed Columns1", "Item", each List.Distinct(Table.Column([Count],"Item"))),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom1", {"Item", each Text.Combine(List.Transform(_, Text.From)), type text}),
    #"Removed Columns2" = Table.RemoveColumns(#"Extracted Values",{"Count"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns2",{"Item", "Code", "Purchase", "OnStock"})
in
    #"Reordered Columns"[/SIZE]

I think you can try to learn step by step how it works
Microsoft Power Query for Excel Help

and here is example again: example file

 

[niam07]

Brilliant, it works. Thanks a lot Sandy. Highly appreciated your help. Just on the output, I also wanted selling column. And the on stock, I wanted it to show "purchase - sell" , and if there is no sell, just the purchase amount. Thanks again.

 

[sandy666]

in PQ editor - Applied steps - delete step: Remove Column1 then reorder columns as you wish and Close&Load

Item	Code	Purchase	Sales	OnStock
Bed	1002​	2​	1​	1​
Dinning Table	1005​	5​	2​	3​
Divan	1006​	2​
Mattress	1004​	4​
Office Chair	1007​	5​	5​	0​
Sofas	1001​	69​	3​	66​
Tea Table	1003​	3​

check example file from post#8