• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help Pivot calculated fields

kjai88

New Member
Dear Chandoo,

Really happy to be associated with you as my first mentor to learn excel.

I have issue to create a calculated filed in pivot. Sample data is as follow:
Pivot reports​
Details​
31-12-20​
31-12-21​
31-12-22​
31-12-23​
31-12-24​
31-12-25​
31-12-26​
Balance sheet​
Asset name​
100​
140
140
140
240
360
500
PL​
Asset name​
10​
20​
30​
(40)​
50​
60​
70​
Cashflow​
Asset name​
(10)​
(20)​
30​
(40)​
(50)​
(60)​
(70)​
Calculated field​
Closing of 2020+PL+CF​

I want a calculated filed for Balance sheet with : "closing of pervious period + PL current period - Cashflow current period"

Regards
Jay
 

vletm

Excel Ninja
kjai88
You have missed 2020 Amounts.
You could delete ... Balance Sheet's 100 and
use 'correct PL-value eg as in this sample.
Q: How do You get Your 'data'?
eg Could it show -Cashflow ... instead Cashflow?
... then those values could be smoother to solve?
 

Attachments

Last edited:

kjai88

New Member
Hi, many thanks for your time, data is correct, as I missed 2019 closing asset value to match the figures.
1) My starting point is Asset value at the end of 2020, i.e; 100 and
2) forecasted values for next 5 years for PL & Cashflow are already in Pivot (cashflow -ve means cash out as an addition to asset)
3) In order to get Forecasted future asset values at the end of respective years, I'm currently doing manual calculation, eventhough the data is in pivot (100+20-(-20)) = 140, need your expertise to automate in pivot as a calculated field.
Please find attached excel
 

Attachments

vletm

Excel Ninja
kjai88
# Did You check Q14's link with #4 reply file?
> There were a sample, which I offered in my previous sample.
# Seems You skipped my idea of Your data too - How...?
> If You cannot get those Cashflow-values as -Cashflow ... then think this sample?
> There can solve those Balance-values with Your data
>> and after that You could .. yes ... get those Balance-values with Pivot-table.
## If Your data is as You've shown ... then why do You would like to use Pivot-table?
 

Attachments

kjai88

New Member
Hi
# Did You check Q14's link with #4 reply file? - WILL CHECK
> There were a sample, which I offered in my previous sample. - WILL CHECK
# Seems You skipped my idea of Your data too - How...?
> If You cannot get those Cashflow-values as -Cashflow ... then think this sample? - NO CASH OUT IS '-' AND CASH IN IS +VE
> There can solve those Balance-values with Your data
>> and after that You could .. yes ... get those Balance-values with Pivot-table. - ANY SUGGESSTION TO USE POWER QUERY TO DO THIS
## If Your data is as You've shown ... then why do You would like to use Pivot-table? - BELOW

- My data is close to 10k rows and increasing,
- i cannot calculate in the raw data for each asset and date as it runs into large data
- the viable option is Take Pivot calculated filed based on dates/period
- will go through Q14
 

vletm

Excel Ninja
kjai88
Please do not shout! = Use capital letters as those should use..
Did You notice that Your given sample data has values only from last days of years and one asset?
... and now, You're writing that there are +10k rows as well as more than one assets ... it's a bit different case that You've written before.
# NO CASH OUT IS '-' AND CASH IN IS +VE
... Your 'data' could show those as I've tried to give an idea.

Even a sample data should be as close as possible with the real data.
... with Your given sample data, there could be many challenges.
 

kjai88

New Member
Thanks for letting me know about Capital letters
my data has
- various assets and various reports bs, pl &cf others
-dates only year end as its just a yearly forecast but for any reporting year say 2022 (current year) have two dates one for YTD and year end.
-Q14 - gone through but not solving my purpose.
Happy share personal contact if u r willing.
 

Attachments

vletm

Excel Ninja
kjai88
Still Your writing and sample sheet do not match - eg dates ... 10k rows.
You seems to add Asset2 with some values, but ... where are eg expected results?
I tried one more time to show something.
What is Your purpose?
Something ... u r ... should someone guess?
 

Attachments

kjai88

New Member
Many thanks for your time and effort.
lastly, Would it be possible to calculate "-PL" and "balance" within Pivot table rather than a separate calculation?
 

vletm

Excel Ninja
kjai88
As I've tried to write few times
Many things would be possible - but -
You have skipped to upload more realistic sample data.
Without that - why should You use Pivot-table?
 
Top