using pivot table to analyze financial statements

[m22phelan]

hello all - I am trying to use pivot tables to analyze income statement, balance sheet and statement of free cash flow of a public company. I transposed data so that it looked like the following spreadsheet and then I tried to use the pivot table to analyze each business segment revenue as a % of total revenue. I used the %of command in the "show value as" button under the pivot table options tab. I wanted to use total revenue as the base but pivot table wouldn't let me use it as the base. Anyone know how to get around this issue?

Thanks and I included transposed spreadsheet below but didn't format very well



[TABLE="width: 935"]
<colgroup><col><col span="4"></colgroup><tbody>[TR]
[TD]Dates[/TD]
[TD]Retail Stores[/TD]
[TD]Port Supply[/TD]
[TD]Direct-to-Consumer[/TD]
[TD]Revenue[/TD]
[/TR]
[TR]
[TD]Mar-08[/TD]
[TD] $ 97.10[/TD]
[TD] $ 9.07[/TD]
[TD] $ 7.09[/TD]
[TD] $ 113.26[/TD]
[/TR]
[TR]
[TD]Jun-08[/TD]
[TD] $ 199.63[/TD]
[TD] $ 12.76[/TD]
[TD] $ 14.28[/TD]
[TD] $ 226.68[/TD]
[/TR]
[TR]
[TD]Sep-08[/TD]
[TD] $ 159.81[/TD]
[TD] $ 9.97[/TD]
[TD] $ 10.48[/TD]
[TD] $ 180.25[/TD]
[/TR]
[TR]
[TD]Dec-08[/TD]
[TD] $ 95.28[/TD]
[TD] $ 7.68[/TD]
[TD] $ 8.11[/TD]
[TD] $ 111.07[/TD]
[/TR]
[TR]
[TD]Mar-09[/TD]
[TD] $ 88.31[/TD]
[TD] $ 6.82[/TD]
[TD] $ 5.84[/TD]
[TD] $ 100.97[/TD]
[/TR]
[TR]
[TD]Jun-09[/TD]
[TD] $ 194.67[/TD]
[TD] $ 8.88[/TD]
[TD] $ 11.82[/TD]
[TD] $ 215.37[/TD]
[/TR]
[TR]
[TD]Sep-09[/TD]
[TD] $ 151.43[/TD]
[TD] $ 7.55[/TD]
[TD] $ 9.18[/TD]
[TD] $ 168.15[/TD]
[/TR]
[TR]
[TD]Dec-09[/TD]
[TD] $ 90.99[/TD]
[TD] $ 5.70[/TD]
[TD] $ 7.25[/TD]
[TD] $ 103.93[/TD]
[/TR]
[TR]
[TD]Mar-10[/TD]
[TD] $ 96.36[/TD]
[TD] $ 6.71[/TD]
[TD] $ 6.49[/TD]
[TD] $ 109.56[/TD]
[/TR]
[TR]
[TD]Jun-10[/TD]
[TD] $ 212.66[/TD]
[TD] $ 9.26[/TD]
[TD] $ 11.48[/TD]
[TD] $ 233.39[/TD]
[/TR]
[TR]
[TD]Sep-10[/TD]
[TD] $ 157.43[/TD]
[TD] $ 6.71[/TD]
[TD] $ 8.41[/TD]
[TD] $ 172.54[/TD]
[/TR]
[TR]
[TD]Dec-10[/TD]
[TD] $ 94.03[/TD]
[TD] $ 5.64[/TD]
[TD] $ 7.64[/TD]
[TD] $ 107.31[/TD]
[/TR]
[TR]
[TD]Mar-11[/TD]
[TD] $ 100.16[/TD]
[TD] $ 6.50[/TD]
[TD] $ 7.16[/TD]
[TD] $ 113.82[/TD]
[/TR]
[TR]
[TD]Jun-11[/TD]
[TD] $ 214.84[/TD]
[TD] $ 8.44[/TD]
[TD] $ 12.68[/TD]
[TD] $ 235.96[/TD]
[/TR]
[TR]
[TD]Sep-11[/TD]
[TD] $ 163.84[/TD]
[TD] $ 7.03[/TD]
[TD] $ 9.40[/TD]
[TD] $ 180.27[/TD]
[/TR]
[TR]
[TD]Dec-11[/TD]
[TD] $ 100.02[/TD]
[TD] $ 5.49[/TD]
[TD] $ 7.88[/TD]
[TD] $ 113.39[/TD]
[/TR]
[TR]
[TD]Mar-12[/TD]
[TD] $ 108.94[/TD]
[TD] $ 6.26[/TD]
[TD] $ 7.11[/TD]
[TD] $ 121.50[/TD]
[/TR]
[TR]
[TD]Jun-12[/TD]
[TD] $ 222.88[/TD]
[TD] $ 8.06[/TD]
[TD] $ 12.63[/TD]
[TD] $ 243.60[/TD]
[/TR]
[TR]
[TD]Sep-12[/TD]
[TD] $ 174.99[/TD]
[TD] $ 6.67[/TD]
[TD] $ 10.26[/TD]
[TD] $ 191.92[/TD]
[/TR]
[TR]
[TD]Dec-12[/TD]
[TD] $ 104.25[/TD]
[TD] $ 5.15[/TD]
[TD] $ 8.89[/TD]
[TD] $ 118.29[/TD]
[/TR]
[TR]
[TD]Mar-13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] 114.2[/TD]
[/TR]
[TR]
[TD]Jun-13[/TD]
[TD][/TD]
[TD][/TD]
[TD]




[/TD]
[TD] 236.8[/TD]
[/TR]
[TR]
[TD]Sep-13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] 193.4[/TD]
[/TR]
[TR]
[TD]

I then crea

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Juan Sombrero]

Hi,

given the end of your massage I'm not sure it's finished,but anyhow...

my frist idea would be to "show value as % of column total.

If this does not work a CALCULATE measure in powerpivot would be the answer.

 

[m22phelan]

Hi,

given the end of your massage I'm not sure it's finished,but anyhow...

my frist idea would be to "show value as % of column total.

If this does not work a CALCULATE measure in powerpivot would be the answer.

txs Juan - the "show value as a % of column" total sums up the column (which in this case sums up dates as field is date based and doesn't give the numbers I'm looking for). I also tried row as that would take different values for the same date and give their % of total but that didn't seem to work - didn't give 100%.

I'm not clear about calculate measure - maybe if you could give an example. I also tried "as a % of" function but I couldn't get the base field to equal total revenues - the only option were dates.

Txs,

Mark