• 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
 
Hi Dear,

Please find attached excel as per your request.
 

Attachments

  • Pivot table help.xlsx
    23.3 KB · Views: 2
kjai88
Your cell G31-value? It's different than others...
Check Q14's link.
and ... L-Q-columns Pivot-table
 

Attachments

  • Pivot table help.xlsx
    26.7 KB · Views: 2
Hi, Yes my calculated value i'm trying is
for year 2021 - Asset (last year closing) + PL (2021)- Cashflow (2021)
100 + 10 - (-10) = 120
 

Attachments

  • Pivot table help-v1.xlsx
    27.2 KB · Views: 4
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

  • Pivot table help-v1.xlsx
    27.4 KB · Views: 3
Last edited:
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

  • Pivot table help-v1.xlsx
    27.8 KB · Views: 1
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

  • Pivot table help-v1.xlsx
    30.6 KB · Views: 1
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
 
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.
 
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

  • Pivot table help-v1.xlsx
    28.4 KB · Views: 2
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

  • Pivot table help-v1.xlsx
    31.8 KB · Views: 2
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?
 
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?
 
Back
Top