Question calculated field pivot table

M

martijnzi

New Member
Joined
Jul 3, 2019
Messages
7
Hello,

I have a question. I would like to calculated ratio's from a database with balancesheets in a pivot-table. The data is as follows:

[TABLE="width: 564"]
<tbody>[TR]
[TD]Company[/TD]
[TD]Balance Sheet no[/TD]
[TD]Description[/TD]
[TD]Value[/TD]
[TD]Year[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]3[/TD]
[TD]Cash[/TD]
[TD]25[/TD]
[TD]2018[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]4[/TD]
[TD]Inventories[/TD]
[TD]35[/TD]
[TD]2018[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]5[/TD]
[TD]Short Term debt[/TD]
[TD]75[/TD]
[TD]2018[/TD]
[/TR]
[TR]
[TD]Facebook[/TD]
[TD]3[/TD]
[TD]Cash[/TD]
[TD]20[/TD]
[TD]2018[/TD]
[/TR]
[TR]
[TD]Facebook[/TD]
[TD]4[/TD]
[TD]Inventories[/TD]
[TD]21[/TD]
[TD]2018[/TD]
[/TR]
[TR]
[TD]Facebook[/TD]
[TD]5[/TD]
[TD]Short Term debt[/TD]
[TD]40[/TD]
[TD]2018[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]3[/TD]
[TD]Cash[/TD]
[TD]100[/TD]
[TD]2017[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]4[/TD]
[TD]Inventories[/TD]
[TD]80[/TD]
[TD]2017[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]5[/TD]
[TD]Short Term debt[/TD]
[TD]50[/TD]
[TD]2017[/TD]
[/TR]
[TR]
[TD]Facebook[/TD]
[TD]3[/TD]
[TD]Cash[/TD]
[TD]60[/TD]
[TD]2017[/TD]
[/TR]
[TR]
[TD]Facebook[/TD]
[TD]4[/TD]
[TD]Inventories[/TD]
[TD]18[/TD]
[TD]2017[/TD]
[/TR]
[TR]
[TD]Facebook[/TD]
[TD]5[/TD]
[TD]Short Term debt[/TD]
[TD]20[/TD]
[TD]2017[/TD]
[/TR]
</tbody>[/TABLE]


What I would like is to have a calculated field in a pivot table that calculates the ratio Cash / Short Term Debt, like this: This is what the result should be:

[TABLE="width: 428"]
<tbody>[TR]
[TD]company[/TD]
[TD]2017[/TD]
[TD]2018[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD] 2,000[/TD]
[TD] 0,333[/TD]
[/TR]
[TR]
[TD]Facebook[/TD]
[TD] 3,000[/TD]
[TD] 0,500[/TD]
[/TR]
</tbody>[/TABLE]

I have been able to create this pivot-table myself, but I had to use a work around. I added two columns to the data, like this:

[TABLE="width: 832"]
<tbody>[TR]
[TD]Company[/TD]
[TD]Balance Sheet no[/TD]
[TD]Description[/TD]
[TD]Value[/TD]
[TD]Year[/TD]
[TD]Cash[/TD]
[TD]Short Term Debt[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]3[/TD]
[TD]Cash[/TD]
[TD]25[/TD]
[TD]2018[/TD]
[TD]25[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]4[/TD]
[TD]Inventories[/TD]
[TD]35[/TD]
[TD]2018[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]5[/TD]
[TD]Short Term debt[/TD]
[TD]75[/TD]
[TD]2018[/TD]
[TD][/TD]
[TD]75[/TD]
[/TR]
[TR]
[TD]Facebook[/TD]
[TD]3[/TD]
[TD]Cash[/TD]
[TD]20[/TD]
[TD]2018[/TD]
[TD]20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Facebook[/TD]
[TD]4[/TD]
[TD]Inventories[/TD]
[TD]21[/TD]
[TD]2018[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Facebook[/TD]
[TD]5[/TD]
[TD]Short Term debt[/TD]
[TD]40[/TD]
[TD]2018[/TD]
[TD][/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]3[/TD]
[TD]Cash[/TD]
[TD]100[/TD]
[TD]2017[/TD]
[TD]100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]4[/TD]
[TD]Inventories[/TD]
[TD]80[/TD]
[TD]2017[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]5[/TD]
[TD]Short Term debt[/TD]
[TD]50[/TD]
[TD]2017[/TD]
[TD][/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]Facebook[/TD]
[TD]3[/TD]
[TD]Cash[/TD]
[TD]60[/TD]
[TD]2017[/TD]
[TD]60[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Facebook[/TD]
[TD]4[/TD]
[TD]Inventories[/TD]
[TD]18[/TD]
[TD]2017[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Facebook[/TD]
[TD]5[/TD]
[TD]Short Term debt[/TD]
[TD]20[/TD]
[TD]2017[/TD]
[TD][/TD]
[TD]20[/TD]
[/TR]
</tbody>[/TABLE]

Then I was able to use a calculated field: = Cash / Short term debt. The result is shown above.

However I am looking for a way to calculate this without changing the data set, because the real dataset is huge and I want to calculate many different ratio's. I need flexibility. I hope anyone has a solution :-).

I hope the problem definition is clear :-)

Kind regards,
Martijn

 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Sort by date Sort by votes
you mean something like this?

Source:

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#FFFF00]Company[/td][td=bgcolor:#FFFF00]Balance Sheet no[/td][td=bgcolor:#FFFF00]Description[/td][td=bgcolor:#FFFF00]Value[/td][td=bgcolor:#FFFF00]Year[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#FFFF00]Apple[/td][td=bgcolor:#FFFF00]3[/td][td=bgcolor:#FFFF00]Cash[/td][td=bgcolor:#FFFF00]25[/td][td=bgcolor:#FFFF00]2018[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#FFFF00]Apple[/td][td=bgcolor:#FFFF00]4[/td][td=bgcolor:#FFFF00]Inventories[/td][td=bgcolor:#FFFF00]35[/td][td=bgcolor:#FFFF00]2018[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#FFFF00]Apple[/td][td=bgcolor:#FFFF00]5[/td][td=bgcolor:#FFFF00]Short Term debt[/td][td=bgcolor:#FFFF00]75[/td][td=bgcolor:#FFFF00]2018[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#FFFF00]Facebook[/td][td=bgcolor:#FFFF00]3[/td][td=bgcolor:#FFFF00]Cash[/td][td=bgcolor:#FFFF00]20[/td][td=bgcolor:#FFFF00]2018[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#FFFF00]Facebook[/td][td=bgcolor:#FFFF00]4[/td][td=bgcolor:#FFFF00]Inventories[/td][td=bgcolor:#FFFF00]21[/td][td=bgcolor:#FFFF00]2018[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#FFFF00]Facebook[/td][td=bgcolor:#FFFF00]5[/td][td=bgcolor:#FFFF00]Short Term debt[/td][td=bgcolor:#FFFF00]40[/td][td=bgcolor:#FFFF00]2018[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#FFFF00]Apple[/td][td=bgcolor:#FFFF00]3[/td][td=bgcolor:#FFFF00]Cash[/td][td=bgcolor:#FFFF00]100[/td][td=bgcolor:#FFFF00]2017[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#FFFF00]Apple[/td][td=bgcolor:#FFFF00]4[/td][td=bgcolor:#FFFF00]Inventories[/td][td=bgcolor:#FFFF00]80[/td][td=bgcolor:#FFFF00]2017[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#FFFF00]Apple[/td][td=bgcolor:#FFFF00]5[/td][td=bgcolor:#FFFF00]Short Term debt[/td][td=bgcolor:#FFFF00]50[/td][td=bgcolor:#FFFF00]2017[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#FFFF00]Facebook[/td][td=bgcolor:#FFFF00]3[/td][td=bgcolor:#FFFF00]Cash[/td][td=bgcolor:#FFFF00]60[/td][td=bgcolor:#FFFF00]2017[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#FFFF00]Facebook[/td][td=bgcolor:#FFFF00]4[/td][td=bgcolor:#FFFF00]Inventories[/td][td=bgcolor:#FFFF00]18[/td][td=bgcolor:#FFFF00]2017[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#FFFF00]Facebook[/td][td=bgcolor:#FFFF00]5[/td][td=bgcolor:#FFFF00]Short Term debt[/td][td=bgcolor:#FFFF00]20[/td][td=bgcolor:#FFFF00]2017[/td][/tr]
[/table]
PivotTable:

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#DCE6F1][/td][td=bgcolor:#DCE6F1]Year[/td][td=bgcolor:#DCE6F1]Values[/td][td=bgcolor:#DCE6F1][/td][td=bgcolor:#DCE6F1][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DCE6F1][/td][td=bgcolor:#DCE6F1]
2017​
[/td][td=bgcolor:#DCE6F1][/td][td=bgcolor:#DCE6F1]
2018​
[/td][td=bgcolor:#DCE6F1][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DCE6F1]Company[/td][td=bgcolor:#DCE6F1] Ratio1[/td][td=bgcolor:#DCE6F1] Ratio2[/td][td=bgcolor:#DCE6F1] Ratio1[/td][td=bgcolor:#DCE6F1] Ratio2[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Apple[/td][td]
2.00​
[/td][td]
1.60​
[/td][td]
0.33​
[/td][td]
0.47​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Facebook[/td][td]
3.00​
[/td][td]
0.90​
[/td][td]
0.50​
[/td][td]
0.53​
[/td][/tr]
[/table]
Ratio1: Cash / Short Term Debt
Ratio2: Cash / Inventories
 
Last edited:
Upvote 0
Hey Sandy666,

That looks perfect! that is what I need :-). How did you do it? And would it also be possible to calculate: (Cash + Inventories) / Short term debt?

BTW, I think you used another definition for ratio 2: Inventories / Short Term Debt.
 
Upvote 0
you are right with Ratio2 :)

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#DCE6F1][/td][td=bgcolor:#DCE6F1]Year[/td][td=bgcolor:#DCE6F1]Values[/td][td=bgcolor:#DCE6F1][/td][td=bgcolor:#DCE6F1][/td][td=bgcolor:#DCE6F1][/td][td=bgcolor:#DCE6F1][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DCE6F1][/td][td=bgcolor:#DCE6F1]
2017​
[/td][td=bgcolor:#DCE6F1][/td][td=bgcolor:#DCE6F1][/td][td=bgcolor:#DCE6F1]
2018​
[/td][td=bgcolor:#DCE6F1][/td][td=bgcolor:#DCE6F1][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DCE6F1]Company[/td][td=bgcolor:#DCE6F1] Ratio1[/td][td=bgcolor:#DCE6F1] Ratio2[/td][td=bgcolor:#DCE6F1] Ratio3[/td][td=bgcolor:#DCE6F1] Ratio1[/td][td=bgcolor:#DCE6F1] Ratio2[/td][td=bgcolor:#DCE6F1] Ratio3[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Apple[/td][td]
2.00​
[/td][td]
1.60​
[/td][td]
3.6​
[/td][td]
0.33​
[/td][td]
0.47​
[/td][td]
0.8​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Facebook[/td][td]
3.00​
[/td][td]
0.90​
[/td][td]
3.9​
[/td][td]
0.50​
[/td][td]
0.53​
[/td][td]
1.025​
[/td][/tr]
[/table]

I used PowerQuery

Code: 
[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Pivot = Table.Pivot(Source, List.Distinct(Source[Description]), "Description", "Value", List.Sum)
in
    Pivot[/SIZE]

Query-Table, not visible in the sheet:
[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td]Company[/td][td]Balance Sheet no[/td][td]Year[/td][td]Cash[/td][td]Inventories[/td][td]Short Term debt[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Apple[/td][td]
3​
[/td][td]
2017​
[/td][td]
100​
[/td][td]null[/td][td]null[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Apple[/td][td]
3​
[/td][td]
2018​
[/td][td]
25​
[/td][td]null[/td][td]null[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Apple[/td][td]
4​
[/td][td]
2017​
[/td][td]null[/td][td]
80​
[/td][td]null[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Apple[/td][td]
4​
[/td][td]
2018​
[/td][td]null[/td][td]
35​
[/td][td]null[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Apple[/td][td]
5​
[/td][td]
2017​
[/td][td]null[/td][td]null[/td][td]
50​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Apple[/td][td]
5​
[/td][td]
2018​
[/td][td]null[/td][td]null[/td][td]
75​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Facebook[/td][td]
3​
[/td][td]
2017​
[/td][td]
60​
[/td][td]null[/td][td]null[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Facebook[/td][td]
3​
[/td][td]
2018​
[/td][td]
20​
[/td][td]null[/td][td]null[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Facebook[/td][td]
4​
[/td][td]
2017​
[/td][td]null[/td][td]
18​
[/td][td]null[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Facebook[/td][td]
4​
[/td][td]
2018​
[/td][td]null[/td][td]
21​
[/td][td]null[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Facebook[/td][td]
5​
[/td][td]
2017​
[/td][td]null[/td][td]null[/td][td]
20​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Facebook[/td][td]
5​
[/td][td]
2018​
[/td][td]null[/td][td]null[/td][td]
40​
[/td][/tr]
[/table]

and created result PivotTable from Query-Table with three Calculated Fields
 
Last edited:
Upvote 0
Hey Sandy,

That looks exactly like what I want. And also, it does exactly the same as the work around. Also, it suggests there is not a more straight forward way to calculate those fields within the standard pivot-table manues.

Now here is the issue, I will use the database for professional issues. I couldnt install power query, because installations are impossible if you are a "user" of a computer in this company(I use office 2010). Do you think there is a way, which doesn't require the installation of applications? Since I work in a large company I think it will be unlikely that the ICT department will install it.

I hope you have more wisdom :-)

Kind regards,
Martijn
 
Upvote 0
Hey Sandy,

Somebody told me here we can also access accel 2016! So the problem should be sovled (i read that it is integrated in there)!

Ill keep you updated if i have my final solution :-)
 
Upvote 0
I wonder why IT departments allow for dangerous vba and do not allow PQ. PQ can not destroy anything but vba yes :laugh:
 
Upvote 0
Problem is solved now. I explored the power query tool and I realized it is brilliant. Thanks so much!!
 
Upvote 0
You are welcome :)

If you are happy you can hit Thanks/Like button at the bottom left corner in the post(s) which helped you

Have a nice day
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
HELP IDENTIFY ORIGINAL START DATE BASED ON CONTINOUS SERVICE RULES
Replies
0
Views
567
mrshuck
M
W
  • Question Question
Macro/VBA code code for creating new excel file for each pivot table from a column filter
Replies
1
Views
490
Anthony47
A
V
  • Solved
Formula to calculate automatically progressive average
Replies
5
Views
505
etaf
etaf
S
  • Question Question
Scan function in calculated field in excel pivot table
Replies
3
Views
298
RoryA
RoryA
M
  • Question Question
Pivot Table Calculated Field Average and Std Deviation (control chart)
Replies
4
Views
284
Sergius
S

Forum statistics

Threads
1,224,817
Messages
6,181,149
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top