Question calculated field pivot table

[martijnzi]

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:

Company	Balance Sheet no	Description	Value	Year
Apple	3	Cash	25	2018
Apple	4	Inventories	35	2018
Apple	5	Short Term debt	75	2018
Facebook	3	Cash	20	2018
Facebook	4	Inventories	21	2018
Facebook	5	Short Term debt	40	2018
Apple	3	Cash	100	2017
Apple	4	Inventories	80	2017
Apple	5	Short Term debt	50	2017
Facebook	3	Cash	60	2017
Facebook	4	Inventories	18	2017
Facebook	5	Short Term debt	20	2017

<tbody>
</tbody>

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:

company	2017	2018
Apple	2,000	0,333
Facebook	3,000	0,500

<tbody>
</tbody>

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:

Company	Balance Sheet no	Description	Value	Year	Cash	Short Term Debt
Apple	3	Cash	25	2018	25
Apple	4	Inventories	35	2018
Apple	5	Short Term debt	75	2018		75
Facebook	3	Cash	20	2018	20
Facebook	4	Inventories	21	2018
Facebook	5	Short Term debt	40	2018		40
Apple	3	Cash	100	2017	100
Apple	4	Inventories	80	2017
Apple	5	Short Term debt	50	2017		50
Facebook	3	Cash	60	2017	60
Facebook	4	Inventories	18	2017
Facebook	5	Short Term debt	20	2017		20

<tbody>
</tbody>

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

 

[martijnzi]

For your convenience: here is a link of the spreadsheet.

https://we.tl/t-wclV1Lhbqq

 

[sandy666]

you mean something like this?

Source:

Company	Balance Sheet no	Description	Value	Year
Apple	3	Cash	25	2018
Apple	4	Inventories	35	2018
Apple	5	Short Term debt	75	2018
Facebook	3	Cash	20	2018
Facebook	4	Inventories	21	2018
Facebook	5	Short Term debt	40	2018
Apple	3	Cash	100	2017
Apple	4	Inventories	80	2017
Apple	5	Short Term debt	50	2017
Facebook	3	Cash	60	2017
Facebook	4	Inventories	18	2017
Facebook	5	Short Term debt	20	2017

PivotTable:

	Year	Values
	2017​		2018​
Company	Ratio1	Ratio2	Ratio1	Ratio2
Apple	2.00​	1.60​	0.33​	0.47​
Facebook	3.00​	0.90​	0.50​	0.53​

Ratio1: Cash / Short Term Debt
Ratio2: Cash / Inventories

 

[martijnzi]

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.

 

[sandy666]

you are right with Ratio2

	Year	Values
	2017​			2018​
Company	Ratio1	Ratio2	Ratio3	Ratio1	Ratio2	Ratio3
Apple	2.00​	1.60​	3.6​	0.33​	0.47​	0.8​
Facebook	3.00​	0.90​	3.9​	0.50​	0.53​	1.025​

I used PowerQuery

[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:

Company	Balance Sheet no	Year	Cash	Inventories	Short Term debt
Apple	3​	2017​	100​	null	null
Apple	3​	2018​	25​	null	null
Apple	4​	2017​	null	80​	null
Apple	4​	2018​	null	35​	null
Apple	5​	2017​	null	null	50​
Apple	5​	2018​	null	null	75​
Facebook	3​	2017​	60​	null	null
Facebook	3​	2018​	20​	null	null
Facebook	4​	2017​	null	18​	null
Facebook	4​	2018​	null	21​	null
Facebook	5​	2017​	null	null	20​
Facebook	5​	2018​	null	null	40​

and created result PivotTable from Query-Table with three Calculated Fields

 

[martijnzi]

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

 

[martijnzi]

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

 

[sandy666]

I wonder why IT departments allow for dangerous vba and do not allow PQ. PQ can not destroy anything but vba yes

 

[martijnzi]

Problem is solved now. I explored the power query tool and I realized it is brilliant. Thanks so much!!

 

[sandy666]

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