Finance Income Statement in Power BI --Paginated Reports?

[Varun511]

HI ,

Was creating Income statement and P&L reports in Power BI but i am not able to achieve format Like below this is pretty common requirement for finance stakeholders

Is there any way to achieve it  ? did any one tried  working on this kind of requirement?

is it possible via paginated reports? if yes can any one share some links or  her/his experience ?

Any help would be highly appreciated

[aj1973]

Hi @Varun511 

I had a different approach, check out my report.

Microsoft Power BI

each row you see in the Matrix is a measure, this way when the user exports the Matrix all he has to do is pivot the table and will have his View as it was in Power bi.

[dcrow5378]

😲Is this a paginated report, or a Power BI app, or both?

[aj1973]

Hi @dcrow5378 

A report published in Power BI App

[TheoC]

Hi @Varun511 

Yes, it can be achieved. Just use a Matrix table. 

Places the Parent Accounts (i.e. Revenue / Expenditure, PBIT, etc) as Rows.  Underneath the Parent Accounts in the Rows field, place your Subaccounts.  Ideally, you should use your Chart of Accounts for establishing this.

If you have a single Column for values and a separate column for distinguishing Debits and Credits, then drag the Column that identifies Debits / Credits in the Columns field of the visual pane.  If you have two separate columns being one for Debits and the other for Credits, then drag both into the Value field (i.e. replace the Amount in the below screenshot with both the Debit and Credit columns).

Using the formatting tools (the paint brush icon above Rows) to modify background colours / text colours, etc.

This will achieve what you're after as I have many client modles for P&L, Balance Sheets, Cash Flows, etc., etc.

Hope this helps 🙂

[Varun511]

Can i do it same way in paginated reports any idea?

[TheoC]

@Varun511 if you've structured your paginated report in line with the P&L, BS, Cash Flow or any other such report, you should have no issues feeding in the relevant fields. However, I do recommend getting a sound understanding of paginated reports prior to going down this path if you haven't as yet.  Here is a starting point: https://docs.microsoft.com/en-us/power-bi/paginated-reports/paginated-reports-quickstart-aw

Hope it helps mate 🙂

[aj1973]

@Varun511 

Paginated reports is mostly used to export tables that exceeds 150000 rows. In your case I don't think it will help you out

[kuji]

Not true at all - would say that is a very narrow view.

Paginated reports can be used for many reporting solutions. such as being able to build reporting packs. Reporting packs are often taken from different parts of the business, e.g. Finance and HR for revenue, costs and headcount reports.

Paginated reports allow you to mix and match different PBI datasets which is something you cannot do in Power BI desktop, This allows a developer to use existing Power BI datasets to build a report which can be more coherent to the end user in a paginated report as they have everything in one place.

The assumption that all end-users want to view a dynamic report on a screen is not always true either - there are many benefits to viewing reports in a print.

In my experience, most reports sent out to leadership/executives are often placed in reporting packs that are in the print mediums - e.g. PowerPoint slides or PDF. When building reports for those 'lower down the food chain' - I have found the preference is for Excel or Power BI. This is likely because they are closer to the data and require data for analytical purposes.

In a nutshell, all users are different and assuming paginated reports is only for datasets with over 150k lines is not good advice.

[Anonymous]

I would agree. Most businesses will need/want both. Why not consolidate all your reporting into one tool if you can. That's my ultimate goal (i.e. build the data model and reports). Then spend time not aggregating data or building/refreshing reports every month focusing on root cause variance analysis/etc. Also, try sending a bank a link to your PBI financial statements app. There are stakeholders that will require a more traditional financial statement.