# using pivot table to analyze financial statements



## m22phelan (Nov 6, 2013)

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




DatesRetail StoresPort SupplyDirect-to-ConsumerRevenueMar-08 $                                                     97.10 $                                                       9.07 $                                                       7.09 $                                                 113.26Jun-08 $                                                  199.63 $                                                     12.76 $                                                     14.28 $                                                 226.68Sep-08 $                                                  159.81 $                                                       9.97 $                                                     10.48 $                                                 180.25Dec-08 $                                                     95.28 $                                                       7.68 $                                                       8.11 $                                                 111.07Mar-09 $                                                     88.31 $                                                       6.82 $                                                       5.84 $                                                 100.97Jun-09 $                                                  194.67 $                                                       8.88 $                                                     11.82 $                                                 215.37Sep-09 $                                                  151.43 $                                                       7.55 $                                                       9.18 $                                                 168.15Dec-09 $                                                     90.99 $                                                       5.70 $                                                       7.25 $                                                 103.93Mar-10 $                                                     96.36 $                                                       6.71 $                                                       6.49 $                                                 109.56Jun-10 $                                                  212.66 $                                                       9.26 $                                                     11.48 $                                                 233.39Sep-10 $                                                  157.43 $                                                       6.71 $                                                       8.41 $                                                 172.54Dec-10 $                                                     94.03 $                                                       5.64 $                                                       7.64 $                                                 107.31Mar-11 $                                                  100.16 $                                                       6.50 $                                                       7.16 $                                                 113.82Jun-11 $                                                  214.84 $                                                       8.44 $                                                     12.68 $                                                 235.96Sep-11 $                                                  163.84 $                                                       7.03 $                                                       9.40 $                                                 180.27Dec-11 $                                                  100.02 $                                                       5.49 $                                                       7.88 $                                                 113.39Mar-12 $                                                  108.94 $                                                       6.26 $                                                       7.11 $                                                 121.50Jun-12 $                                                  222.88 $                                                       8.06 $                                                     12.63 $                                                 243.60Sep-12 $                                                  174.99 $                                                       6.67 $                                                     10.26 $                                                 191.92Dec-12 $                                                  104.25 $                                                       5.15 $                                                       8.89 $                                                 118.29Mar-13                                         114.2Jun-13




                                         236.8Sep-13                                         193.4

I then crea



<colgroup><col><col span="4"></colgroup><tbody>

</tbody>


----------



## Juan Sombrero (Nov 7, 2013)

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 (Nov 7, 2013)

Juan Sombrero said:


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



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


----------

