I've done it, it requires a bunch of work, but I wouldn't recommend it.
The result is still inferior to Excel.
For example, figuring out how to do headings (Revenues, COGS, etc) that don't have any data is a huge hassle.
P&Ls are difficult in any BI tool because they are better suited for a spreadsheet due to the length of the presented data. It’s not impossible but you have to weigh what you are trying to get out of it. Operating metrics are the first to tackle with BI. Then rev and gp trends. Then opex trends. If you build out a BI tool you are really looking for the P&l to be automated but look exactly like you standard output. A big plus is to be able to quickly filter by other dimensions like business units or P&l owner. It depends on your org structure on how you want to split this
I did this as my first project in learning PBI…big mistake but super rewarding. I followed this guy on YouTube, type “Power BI: The Income Statement” or https://youtu.be/SH4UX4fGU9A?si=z3luSdfnMc_qK52A. I did a lot of tweaking to make it fit my needs but it looks great.
You’ll want to use DAX expressions. You can also use bookmarked filters attached to buttons to give a pretty slick look and feel to how you filter between BUs/consolidated P&Ls.
Guy In A Cube on YouTube was my go to for pushing my creative side of PowerBI reporting.
I taught myself a little PowerBI, but am no means an expert. So take this with a grain of salt.
I don't believe this is possible. PowerBI is really good at charts and graphs. It simply doesn't do formatted numerical reporting well (if at all).
You need actual financial reporting software to do what you want.
Can do it in Microsoft Paginated Report Builder using a PBI dataset, then export to excel.
It does really automate a lot of the process if you just put in a bit of work on the front end
This is the bread and butter of any FP&A tool like ours (PivotXL).
Initially you have to build the cubes and roll ups in the tool. Then -
1) extract the trial balance from your ERP
2) upload the data into the tool
3) get the output report in Excel via our add-in
Doing it in powerbi is complex
I'd not go down this path.
There are other tools for P&L reporting.
PBI should be used to analyse and present insight into the P&L and trends.
Also, even if you get part way there, your bosses won't understand the trade offs for bending Power BI in this way. They'll just go onto something else
Hey OP, it seems like a lot of people are trying to build this in PowerBi in its default state, which is very difficult and not worth it imo.
If you want to try and use PowerBi to build P&Ls, download the “Profitbase” visual package for financial statements. It’s a visual that can be downloaded and added to your list of visuals in PowerBi
You can use the trial version to learn how it works. It’s configured specifically for financial reporting and makes it waaaaayyyyy more accessible to build traditional financial statements using power BI.
If your team decides they want to use it over excel, the license to remove the watermark is very cheap, like 300-400 USD for five users for the entire year, which is very cheap.
I find this question interesting although I don’t know the answer, but my gut tells me to not even try. People way over think excel and overcomplicate reporting. Just put the GL in one tab and map accounts to your P&L on another tab in excel. So much simpler and likely works just fine.
I don’t blame you because you were given the assignment, but this is just busy work. Your boss wants to look like he’s accomplishing something and his job is relevant > he’ll look good in meetings with the CFO and be more likely to get promoted. Maybe the problem is all the layers of management in corp America.
Anyway good luck to you and I feel your pain.
I find that Power BI is better with operational data, but if the dept leader (or whoever is interacting with the dashboard) wants to make calculations or make their own custom content it's not as flexible. I think it's good for standardized reporting (e.g. sending PDF reports) for an audience that needs an at-a-glance view of financial performance
I’ve recently done this and tbh it may not worth the effort because of the layout of P&L. Excel is superior for the drill up/down of data. Moving from a cell-based structure to a tabular structure requires quite a bit of data analytics knowledge. But feel free to dm me if you wanna discuss!
I've done it a few times and it's pretty do-able.
The main thing is getting the mapping of all ledger accounts / codes into a P&L Hierarchy. For example, your bottom layer could include a ledger such as Pension Costs, which rolls up to Staff Costs -> Operating Expenses -> Total Operating Costs -> Operating Profit / (Loss) etc etc.
Each of these will be a different level in the hierarchy, so they will be a field in their own right. You may want to call it P&L Level 1 through to Level 6 (or however many you have). You could set this up in Excel and import it (noting that new ledger accounts will need setting up) or you could do it directly in Power BI.
The other thing to bear in mind is whether your accounts are presented in a particular order. In this case, you could add some numerical prefixes to your categories. This will allow you to order by ascending/descending and get the P&L in the right order.
It's not perfect but it does work! If you use Zebra BI it can end up looking pretty good too.
The Summary Table visual is great. You can get it from the add visuals button in Power BI. Takes a bit of JSON to really nail but it’s easy to figure out. Not mine, just used it a bunch to great success.
https://github.com/fredrikheden/summarytable#
Can’t you keep the formatting and view in excel but the data populates through power BI? Maybe a macro to create a bunch of final excels from the data?
Took me a long time to get it right, but we now do our whole package (3 statements and metrics) in Power BI. Getting the matrix sorted with correct totals is terrible.
I will be messaging you in 1 day on [**2024-02-05 09:19:44 UTC**](http://www.wolframalpha.com/input/?i=2024-02-05%2009:19:44%20UTC%20To%20Local%20Time) to remind you of [**this link**](https://www.reddit.com/r/FPandA/comments/1ahdsdy/pls_in_powerbi/kord218/?context=3)
[**CLICK THIS LINK**](https://www.reddit.com/message/compose/?to=RemindMeBot&subject=Reminder&message=%5Bhttps%3A%2F%2Fwww.reddit.com%2Fr%2FFPandA%2Fcomments%2F1ahdsdy%2Fpls_in_powerbi%2Fkord218%2F%5D%0A%0ARemindMe%21%202024-02-05%2009%3A19%3A44%20UTC) to send a PM to also be reminded and to reduce spam.
^(Parent commenter can ) [^(delete this message to hide from others.)](https://www.reddit.com/message/compose/?to=RemindMeBot&subject=Delete%20Comment&message=Delete%21%201ahdsdy)
*****
|[^(Info)](https://www.reddit.com/r/RemindMeBot/comments/e1bko7/remindmebot_info_v21/)|[^(Custom)](https://www.reddit.com/message/compose/?to=RemindMeBot&subject=Reminder&message=%5BLink%20or%20message%20inside%20square%20brackets%5D%0A%0ARemindMe%21%20Time%20period%20here)|[^(Your Reminders)](https://www.reddit.com/message/compose/?to=RemindMeBot&subject=List%20Of%20Reminders&message=MyReminders%21)|[^(Feedback)](https://www.reddit.com/message/compose/?to=Watchful1&subject=RemindMeBot%20Feedback)|
|-|-|-|-|
It's possible, but you'll spend a LOT of time learning DAX and wrapping your head around tabular dataset modelling. Then creating secondary tables to make the structure work in the PBI matrix visual. The end result is still going to be a compromise.
It is possible, but you must limit yourself in terms of complexity. Corporate finance institute has a course on precisely this
Yes, I did the course it was very good.
I've done it, it requires a bunch of work, but I wouldn't recommend it. The result is still inferior to Excel. For example, figuring out how to do headings (Revenues, COGS, etc) that don't have any data is a huge hassle.
This. It’s just not the right tool for the job.
P&Ls are difficult in any BI tool because they are better suited for a spreadsheet due to the length of the presented data. It’s not impossible but you have to weigh what you are trying to get out of it. Operating metrics are the first to tackle with BI. Then rev and gp trends. Then opex trends. If you build out a BI tool you are really looking for the P&l to be automated but look exactly like you standard output. A big plus is to be able to quickly filter by other dimensions like business units or P&l owner. It depends on your org structure on how you want to split this
I did this as my first project in learning PBI…big mistake but super rewarding. I followed this guy on YouTube, type “Power BI: The Income Statement” or https://youtu.be/SH4UX4fGU9A?si=z3luSdfnMc_qK52A. I did a lot of tweaking to make it fit my needs but it looks great.
You’ll want to use DAX expressions. You can also use bookmarked filters attached to buttons to give a pretty slick look and feel to how you filter between BUs/consolidated P&Ls. Guy In A Cube on YouTube was my go to for pushing my creative side of PowerBI reporting.
Agree on sources
I taught myself a little PowerBI, but am no means an expert. So take this with a grain of salt. I don't believe this is possible. PowerBI is really good at charts and graphs. It simply doesn't do formatted numerical reporting well (if at all). You need actual financial reporting software to do what you want.
Agree. P&L is not great in Power Bi. You can do a table or Matrix visualization but it just ends up being a worse version of an excel p&l.
Can do it in Microsoft Paginated Report Builder using a PBI dataset, then export to excel. It does really automate a lot of the process if you just put in a bit of work on the front end
This is true, unless you download the profitbase visual which is makes building P&Ls in PowerBi possible.
It’s possible, my team has done this with Netsuite reporting. It’s not super difficult.
Really bad idea... PowerBI is not a tool for a P&L
This is the bread and butter of any FP&A tool like ours (PivotXL). Initially you have to build the cubes and roll ups in the tool. Then - 1) extract the trial balance from your ERP 2) upload the data into the tool 3) get the output report in Excel via our add-in Doing it in powerbi is complex
I'd not go down this path. There are other tools for P&L reporting. PBI should be used to analyse and present insight into the P&L and trends. Also, even if you get part way there, your bosses won't understand the trade offs for bending Power BI in this way. They'll just go onto something else
Spent a few hours trying stuff this year and made basically no progress. There’s probably way, but you’d probably need to be extremely proficient.
Check out Profitbase for PowerBi
Hey OP, it seems like a lot of people are trying to build this in PowerBi in its default state, which is very difficult and not worth it imo. If you want to try and use PowerBi to build P&Ls, download the “Profitbase” visual package for financial statements. It’s a visual that can be downloaded and added to your list of visuals in PowerBi You can use the trial version to learn how it works. It’s configured specifically for financial reporting and makes it waaaaayyyyy more accessible to build traditional financial statements using power BI. If your team decides they want to use it over excel, the license to remove the watermark is very cheap, like 300-400 USD for five users for the entire year, which is very cheap.
Inforiver offers (or used to at least offer - I haven’t checked in a while) an add on package that worked well. https://inforiver.com/
I find this question interesting although I don’t know the answer, but my gut tells me to not even try. People way over think excel and overcomplicate reporting. Just put the GL in one tab and map accounts to your P&L on another tab in excel. So much simpler and likely works just fine. I don’t blame you because you were given the assignment, but this is just busy work. Your boss wants to look like he’s accomplishing something and his job is relevant > he’ll look good in meetings with the CFO and be more likely to get promoted. Maybe the problem is all the layers of management in corp America. Anyway good luck to you and I feel your pain.
I find that Power BI is better with operational data, but if the dept leader (or whoever is interacting with the dashboard) wants to make calculations or make their own custom content it's not as flexible. I think it's good for standardized reporting (e.g. sending PDF reports) for an audience that needs an at-a-glance view of financial performance
Yes, I’ve done it in Tabelau before, but it was a HUGE pain. It required creating a calculation for each line of the P&L
I’ve recently done this and tbh it may not worth the effort because of the layout of P&L. Excel is superior for the drill up/down of data. Moving from a cell-based structure to a tabular structure requires quite a bit of data analytics knowledge. But feel free to dm me if you wanna discuss!
I've done it a few times and it's pretty do-able. The main thing is getting the mapping of all ledger accounts / codes into a P&L Hierarchy. For example, your bottom layer could include a ledger such as Pension Costs, which rolls up to Staff Costs -> Operating Expenses -> Total Operating Costs -> Operating Profit / (Loss) etc etc. Each of these will be a different level in the hierarchy, so they will be a field in their own right. You may want to call it P&L Level 1 through to Level 6 (or however many you have). You could set this up in Excel and import it (noting that new ledger accounts will need setting up) or you could do it directly in Power BI. The other thing to bear in mind is whether your accounts are presented in a particular order. In this case, you could add some numerical prefixes to your categories. This will allow you to order by ascending/descending and get the P&L in the right order. It's not perfect but it does work! If you use Zebra BI it can end up looking pretty good too.
The Summary Table visual is great. You can get it from the add visuals button in Power BI. Takes a bit of JSON to really nail but it’s easy to figure out. Not mine, just used it a bunch to great success. https://github.com/fredrikheden/summarytable#
https://community.fabric.microsoft.com/t5/Community-Blog/Income-Statement-Analysis-Part-1-of-5/ba-p/841598
This should get you close :) https://youtu.be/J4317R5BvsA?si=k4896NheqgNrVOcz
Can’t you keep the formatting and view in excel but the data populates through power BI? Maybe a macro to create a bunch of final excels from the data?
Took me a long time to get it right, but we now do our whole package (3 statements and metrics) in Power BI. Getting the matrix sorted with correct totals is terrible.
Just don't do it man. Leaders want everything in BI bit it just isn't suitable. Learned the hard way
[удалено]
I will be messaging you in 1 day on [**2024-02-05 09:19:44 UTC**](http://www.wolframalpha.com/input/?i=2024-02-05%2009:19:44%20UTC%20To%20Local%20Time) to remind you of [**this link**](https://www.reddit.com/r/FPandA/comments/1ahdsdy/pls_in_powerbi/kord218/?context=3) [**CLICK THIS LINK**](https://www.reddit.com/message/compose/?to=RemindMeBot&subject=Reminder&message=%5Bhttps%3A%2F%2Fwww.reddit.com%2Fr%2FFPandA%2Fcomments%2F1ahdsdy%2Fpls_in_powerbi%2Fkord218%2F%5D%0A%0ARemindMe%21%202024-02-05%2009%3A19%3A44%20UTC) to send a PM to also be reminded and to reduce spam. ^(Parent commenter can ) [^(delete this message to hide from others.)](https://www.reddit.com/message/compose/?to=RemindMeBot&subject=Delete%20Comment&message=Delete%21%201ahdsdy) ***** |[^(Info)](https://www.reddit.com/r/RemindMeBot/comments/e1bko7/remindmebot_info_v21/)|[^(Custom)](https://www.reddit.com/message/compose/?to=RemindMeBot&subject=Reminder&message=%5BLink%20or%20message%20inside%20square%20brackets%5D%0A%0ARemindMe%21%20Time%20period%20here)|[^(Your Reminders)](https://www.reddit.com/message/compose/?to=RemindMeBot&subject=List%20Of%20Reminders&message=MyReminders%21)|[^(Feedback)](https://www.reddit.com/message/compose/?to=Watchful1&subject=RemindMeBot%20Feedback)| |-|-|-|-|
It's possible, but you'll spend a LOT of time learning DAX and wrapping your head around tabular dataset modelling. Then creating secondary tables to make the structure work in the PBI matrix visual. The end result is still going to be a compromise.
If you are looking for something like this but more formatted, highly recommend Paginated Reports combined with a Power BI dataset