Sum Down in Pivot Table With The Same Name as Criteria

[muhammad susanto]

hi all...

i need your help, how to setting/make it in pivot table like this sample below :

Excel 2007 32 bit

	A​	B​	C​	D​	E​	F​
1​	date	name	amount	transact-1	transact-2	transact-3
2​	15/10/2015​	jack	100​
3​	15/10/2015​	mike	150​
4​	15/10/2015​	fikka	200​
5​	15/10/2015​	thomas	400​
6​	12/12/2015​	mike		50​
7​	15/01/2016​	mike			10​
8​	03/03/2016​	jack				25​
9​
10​	after using pvot table (e.g.)
11​	date	name	sum of oustanding
12​	15/10/2015​	jack	100​
13​	03/03/2016​	jack	75​
14​	15/10/2015​	fikka	200​
15​	15/10/2015​	mike	150​
16​	12/12/2015​	mike	100​
17​	15/01/2016​	mike	90​
18​
19​	note :
20​	sum of oustanding = amount- (transact1+transact2+transact3)
21​	for example :
22​	for jack = 100-25=75
23​	etc..

Sheet: Sheet1

any help, much assistance..

m.susanto

 

[muhammad susanto]

You have to put the name as the first row field and then the date. It will not work in the original layout you requested, as I mentioned to begin with.

Actually, I stand corrected - as long as the Summing field is set to display as a running total within date, it will work with the row fields in either order.

for row name of Jack the summing total is -25 should be 75 (100-25)
where is i missing..

here new link https://app.box.com/s/2pmokg4et9m1546xebcw4dzz9s5fbv9q

 

[RoryA]

You didn't do two of the things I said:
1. Ungroup the date field. (right click it and Ungroup...)
2. Change the value field to show as a running total. (Right click the value field, choose Value field settings, then use the Show Values As tab)

You will also need to make sure your dates are in ascending order.

 

[muhammad susanto]

You didn't do two of the things I said:
1. Ungroup the date field. (right click it and Ungroup...)
2. Change the value field to show as a running total. (Right click the value field, choose Value field settings, then use the Show Values As tab)

You will also need to make sure your dates are in ascending order.

hi Rory, i have do what you say, but the running total is not correct...
for name's Jack the summing total is -25 should be 75 (100-25)
i'm really stuck now....i hope someone could help me ...

 

[RoryA]

Please post the latest file. You must have missed a step.

 

[muhammad susanto]

Please post the latest file. You must have missed a step.

hi Rory,
https://app.box.com/s/mte4wyti7n34a5n7m7kpa98qwnr78gk1

 

[RoryA]

You need to remove the other row fields besides name and date, otherwise those groupings prevent the running total from working.

 

[muhammad susanto]

You need to remove the other row fields besides name and date, otherwise those groupings prevent the running total from working.

hi Rory, worked great!! my question is why must do like that?
i mean that's not stable or inconsistent, when i grouping date by month, the result is not correctly..
how to make is robust /consistent!!

 

[muhammad susanto]

anyone help me!!