March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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
Yes .it's completely doable in paginated reports (which are really just old SQL server SSRS rebadged). They can produce "pixel perfect" output if you need it for documents rather than interactive matrixes.
Hi @Varun511
I had a different approach, check out my report.
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.
Regards
Amine Jerbi
If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook
😲Is this a paginated report, or a Power BI app, or both?
Hi @dcrow5378
A report published in Power BI App
Regards
Amine Jerbi
If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook
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 🙂
If I have posted a response that resolves your question, please accept it as a solution to formally close the post.
Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!
Want to connect?www.linkedin.com/in/theoconias
Can i do it same way in paginated reports any idea?
@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 🙂
If I have posted a response that resolves your question, please accept it as a solution to formally close the post.
Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!
Want to connect?www.linkedin.com/in/theoconias
Paginated reports is mostly used to export tables that exceeds 150000 rows. In your case I don't think it will help you out
Regards
Amine Jerbi
If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook
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.
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
90 | |
89 | |
83 | |
73 | |
49 |
User | Count |
---|---|
167 | |
149 | |
98 | |
73 | |
57 |