Power BI P&L Report Tutorial For Beginners: Bar Charts + Conditional Formatting + Waterfall + Dax
Vložit
- čas přidán 6. 08. 2024
- Create Interactive Visuals using tables & charts from scratch with this step by step tutorial specially designed for Power BI Beginners: including Dax for YOY analysis, This Year, Last Year and YOY % Growth.
In this video I will walk you through the 3 fundamental steps to create a P&L report (or Income Statement or Financial Statement) in Power BI.
Step 1: Get the data model set up correctly.
Step 2: Get the DAX measures set up correctly.
Step 3: Create the reports, tables & charts.
In Step 1, I will show you the dimension tables and fact tables that you will need to create a simple P&L model in Power BI, as well as the ideal set up using 1 to many relationship star schema.
In Step 2, I will show you the important DAX measures that you will need to create to enable YOY comparison, This Year vs Last Year in absolute $ term as well as in % term. Please note, if you are not yet 100% comfortable with DAX and find the DAX explanation a little bit confusing, please invest the time to learn the CALCULATE function and watch the video that I made last week.
In Step 3, I will show you how you can leverage various Power BI data visualisation to tell a story and provide useful insights for your audience… answering frequently asked questions such as… Why did profit go up or down? By Cost Centre, By GL line, etc… using:
- Tables with conditional formatting to show your P&L
- Bar Charts with conditional formatting for benchmarking analysis
- Waterfall charts for variance analysis
- Line charts for trend analysis
I will show you how to build those visualisations from scratch, so that you can create super useful and sexy looking charts and tables in your Power BI P&L report.
As a bonus at the end, I will also show you how to change the colour theme really quickly and efficiently.
Hope you find this video useful and hope that it will inspire you to create a financial statement or P&L statement or P&L report in Power BI in the future.
Cheers,
Isabella Susilowati
#LighthouseAnalytix
#PowerBITutorialForBeginners
Link to last week CALCULATE DAX video:
• Power BI DAX Demystifi...
Timelines:
00:00 Introduction
01:36 P&L Report Overview
03:02 How to create P&L Report in Power BI
03:21 Step 1 - Data Model Set Up
05:45 Step 2 - DAX Measures Set Up
08:27 Step 3 - Creating the Tables & Charts
09:21 P&L Table
11:30 P&L Table With Conditional Formatting
12:52 P&L Table Cloning
14:43 Bar Chart With Conditional Formatting
17:51 Waterfall Chart
21:30 Trend Charts
26:22 Interaction & Tidy Up
28:24 Changing Colour Theme
28:50 Epilogue
LA50
Hi all,
If you want to download the PBIX working file... you can go to mywebsite: Lighthouse Analytix.com.
Go to free tutorial section.
Look for videos on:
Power BI Waterfall Chart + Field Parameter = Enhanced P&L Report and variance analysis in PBI !!!
I used the same pbix file... that you can use to practice along..
Cheers,
Isabella.
Hey Isa! Loved the content and execution of the video. And your voice is absolutely soothing!!
Lol - hope you are not falling asleep !!!
Very useful, thank you!
You're welcome!
Very useful ... your tutorials are great, professional and very simple
Glad you like them!
Amazying! It woud be great to have a similar one comparing budget vs real data.
Coming soon!
Great tutorial. Thanks for sharing🌹🌹👍🏻👍🏻
Thank you for the feedback! Cheers!
Terima kasih Isabella, saat ini saya sedang mulai untuk mempelajari PBI, suatu keberuntungan bagi saya menemukan Chanel YT ini yang sangat luar biasa membahas analisa keuangan. Sepertinya saya akan banyak download file di lighthouse untuk bahan saya mempelajari PBI. Sengaja saya komentar pakai bahasa Indonesia, karena nama kamu ada nuansa Indonesia nya.
Semangat maju terus belajar PBI ya !!
"karena nama kamu ada nuansa Indonesia nya" agree hahaha...kita pelajari n like video nya Bang...jarang nih yang kasih tutorial gini, hanya beberapa yang dari indo sepertinya...
setelah kulihat namanya sepertinya orang Indo..saya pikir orang luar...Makasih Mba Isabella, ini sangat membantu, setelah melihat tutorial ini ada yang harus saya perbaiki dari struktur data dan measure yang tersebar dimana mana hahaha,harusnya ku grouping :)😍
Terima kasih atas komentarnya... and all the best ya... :)
Hello. Great video. For the tables how to hide/ remove the Totals row.
Thanks.
Click your table, then go to the visualization pane on the right.
Click format visual, then scroll down to Total.
There is an option to turn off value.
Good luck !!!
Hi! Thank you- your videos are super helpful. If my data(coming from HFM) is all positive values (both expense and revenue), is there a way to covert expenses to negative to create performance variance measure? Thanks again !
I would create a P&L measure that says, if expense then -1*value, if revenue then 1*value.
@@LighthouseAnalytix thanks!!
Great and excellent Video. Do u have a link to download the source & practice please. Thanks in advance
Not yet.. but in the midst of setting up a website to make it easier to do so in the future... So stay tuned.
Good example. However in my experience most difficult part is data model preparation. It is relatively easy to do it with 5-10 GL accounts. But what if you have 50+ accounts? What if your GL ledger gets new accounts from time to time? What if you have a multi-level structure of your GL?
Agree... Data model preparation is key. Where possible link your PBI data model to your source system or your data warehouse. That way, any new GL accounts as well as changes in GL hierarchy will automatically flow into your Power BI reports.
hi very good presentation, can we get a link of the data file to test the same. Thank you
You can get it from my website. www.lighthousenalytix.com
Then go to tutorial:
Power BI Waterfall Chart + Field Parameter = Enhanced P&L Report and variance analysis in PBI !!!
I used the same data set for both videos.
Hi, thanks for information.. I wish to take personalized training.. please advise
Hi ... you are welcome... and thank you for reaching out to me. I'm in the process of setting something up so that you can see my availability and book me for 1:1 session via my website.
@@LighthouseAnalytix thanks would appreciate if you can share link once it is available
great! how we can download your file?
hi - we'll post it in our website - in the next 48h.
In the meantime, you can also download the pbix file that we used for PBI waterfall chart + field parameters. I used the same pbix file for both videos.
Where is your date
you can add them if required.