Profit and Loss with PowerPivot

[SimonNU]

Hi Guys

I am wanting to update an old bloated SUMIFS'd Profit and Loss statement to PowerPivot. I'm brand new to PowerPivot so I am not certain of it's capabilities, I am therefore looking for insight and/or direction.

Thus far, I have replicated the P&L and added 4 slicers to drill-down. Unfortunately, I have hit a wall at the stage where I require subtotalling & blank spaces throughout the Pivot Table. For example, take the bold row labels below:

Is PP capable of replicating a report similar to the above that can be easily updated monthly?

• If so, how would I calculate subtotal rows, e.g. Total Income, Total Expenses, Total Overheads etc...?
• Will they update according to the filters selected with Slicers?
• How do I add them as rows?

If it's helpful, my data is in 1 table structured as:
Year | P&L Row Label | Slicer Filter 1 | Slicer Filter 2 | Slicer Filter 3 | Slicer Filter 4 | Period 1 | Period 2 | Period 3 | Period 4 | Period 5 | Period 6 | Period 7 | Period 8 | Period 9 | Period 10 | Period 11 | Period 12

Thanks for your input!

 

[Tianbas]

We do something similar with CUBE formulas. With a CUBE formula you can pull any data from your PowerPivot data model in a single excel cell. You can design the layout of your report in Excel (including subtotals) but still use slicer and get the data updated from the PowerPivot model. The 2 formulas you normally need are CUBEVALUE and CUBEMEMBER. Just search for it at microsoft or powerpivotpro.

To get started with CUBE formulas just build a pivot table (e.g the direct Expense part of your P&L) click on a field in the table go to Options->OLAP Tools->Convert to formulas

 

[GDRIII]

New to PP...Go get Rob Collie's books "DAX Formulas for PowerPivot", "Power Pivot Alchemy", and add Kasper de Jonge's book "Dashboarding and Reporting with PP" that is an End to End design and create Financial Dashboard.

That right there is the holy trinity of PowerPivot learning.

Also happens to be part of todays post at PowerPivotPro.com

Happy PowerPivoting...

 

[scottsen]

I think you are going to hate that table structure. Any chance it's just coming from SQL and easily unpivoted? the other dudes are probably correct in that to format your report to look similiar, you may need to do fancy cube formula work.

However, they may be jumping the gun in that... are you even getting the NUMBERS you need yet?

 

[GDRIII]

Couldn't you make a stand alone listing your Account hierarchy and link it....

Col A Col B Col C
Ordinary Income Income Rent received
Ordinary Income Income Sales Income

And then just nest (After renaming appropriately) A, B, C on rows and get the result in the Subtotals?

 

[rwilson2014]

This might help

Profit & Loss–The Art of the Cascading Subtotal « PowerPivotPro

There are three parts to this method. Make sure you work your way through all three - I found the technique very helpful.

Cheers

 

[SimonNU]

Thanks all!

I took Tianbas's advice and converted my table to formulas which allowed me to create it precisely as I wanted quite quickly.

Unfortunately, after I'd finished, I discovered that excel functionality is disabled on our SharePoint 2010 site so I'm unable to distribute the workbook. Just my luck!

 

[Tianbas]

Push your IT department I do not really like Sharepoint but it is currently the best way to distribute PowerPivot Files to the management.

 

[scottsen]

Depending on your situation, Office365 may be a relatively inexpensive solution. Yes, it has its own set of challenges...