How to get example data for power bi P&L and prepare the data

[@seyiabayomi]

I've not started watching your other videos, but I'm delighted to have come across your channel. Looking forward to seeing more on this subject.

[@controloverreports]

Thank you. Hope it give you some extras to your power bi journey.

[@alexrosen8762]

This series is great because it involves all the important steps to make a complete P&L report. Really useful. Thanks a lot

[@controloverreports]

Thank you for your kind words! My pleasure

[@vishalsadawarte1783]

Wow! Amazing tutorial. Thanks for the efforts you invested in creating this tutorial.
I have few doubts, if you don't mind, can you please make a video/explain it here?
Q1. How did you create SubHeaderDetails, SubHeadorSort, Sign and Report Sign? I mean, did you manually had a look into the details and gave those figures or automated it somehow?
Q2. What is the use of creating the Finance Pivot table? Where are we going to use it?

[@controloverreports]

Hi Vishal
Thanks, and good questions
I start with Q2
The use of creating the Finance Pivot Table is for own development and analyze in the building phase. To ensure I could see which accounts (Accountkeys) had actual bookings (lowest level) on them. So I used it to be sure I did the flattening the right way, because the original accountstable is a parent-child hierarchy we flattened (by the way see my other video’s for not manual way to flatten in M and Dax)
Also in this demo-file from microsoft the accounts table contains AccountKeys for other performance indicators than Accounts like 96 Headcount and 99 Square Footage. I wanted to be sure I could remove/filter them out
Q1
The SubHeadersDetails is just a formule see file ‘03 PL Adventure Works to FS Data’
=IF([@AccLevel4][@AccLevel5],1,0) . Checking if accountslevel 4 and 5 are the same or not. Handy to know with expanding later on in visual, so you could suppress the lowest level if they are the same
The subheadersort Manually.
The Sign and report Sign, In this Case manually, (some Intermediate/Expert addition below)
The Sign and report Sign only because of microsoft made everything already positive (in datawarehouse). Normally if you connect (or extract) from a bookkeeping system it is not necessary (revenue negative, costs positive). You could just make one DAX measure and the result is already adding up.
Alternative…. skip report column and only use sign column. In real life you could also skip sign column and do it complete in DAX measure. The next measure is the example of skipping report sign.
Actuals Frank =
VAR _SelectedHeader = SELECTEDVALUE(Header[Header])
VAR _ActualAddsUpxmin1 = Sumx(
values(Accounts[Sign]),
[Actual]*Accounts[Sign]*-1)

VAR _GrossMargin = CALCULATE([Actual Adds Up],
Header[Header] = "Revenue"
|| Header[Header] = "Cost of Sales",
REMOVEFILTERS(Header)
)
VAR _OperatingProfit = CALCULATE([Actual Adds Up],
REMOVEFILTERS(Header),
Header[Header] = "Revenue"
|| Header[Header] = "Cost of Sales"
|| Header[Header] = "Operating Expenses"
)
VAR _NetIncome = CALCULATE([Actual Adds Up],
REMOVEFILTERS(Header),
Header[Header] = "Revenue"
|| Header[Header] = "Cost of Sales"
|| Header[Header] = "Operating Expenses"
|| Header[Header] = "Other Income and Expense"
|| Header[Header] = "Taxes"
)

VAR _Result = SWITCH(TRUE(),
_SelectedHeader = "Revenue", [Actual Adds Up],
_SelectedHeader = "Cost of Sales", _ActualAddsUpxmin1,
_SelectedHeader = "Gross Margin", _GrossMargin,
_SelectedHeader = "Operating Expenses",_ActualAddsUpxmin1,
_SelectedHeader = "Operating Profit", _OperatingProfit,
_SelectedHeader = "Other Income and Expense", [Actual Adds Up],
_SelectedHeader = "Taxes", _ActualAddsUpxmin1,
_SelectedHeader = "Net Income", _NetIncome,
[Actual Adds Up]
)

return _Result

[@vishalsadawarte1783]

@@controloverreports WOOH! I didn't expect to get the answer, Thanks a lot for the detailed explanation. It's really helpful.

[@paolod6853]

Hey Frank - a lot of things here - lovely!

[@zakariafadili2762]

Thank you for Control Over Reports B.V. channel.. i used dax to flatten the table and add six AccLevel columns , but how can i use power bi to add SubHeadersDetail, SubHeaderSort, Sign and Report columns and how can i create DimHeader and DimRatio using Power BI ?

[@controloverreports]

HI Zakaria Dadili. In this sample-dataset (adventurework) there is no DimHeader-table (or DimRatio) that i am aware of (see Zip-file CSV's). So you need to add it. You can type it directly in Power BI (Enter Data on the Home tab of Power BI desktop) or use a table (in this example i used an excel table). If you (try) create the table DimHeader in DAX using group by (in Dax) on COA you will miss the subtotals (Gross profit margin etc) rows. Also you will miss the calc type column but there is a workaround for. The subheaderesdetail isn't necessary but nice to have to supress blank lower level collapse (if is AccLevel5 = Acclevel4 then 0 else 1 in power query, i think you can do this in adding a conditional column) . The sign and report sign column can be replaced by multiple 1 *-1 directly in a measure (instead f a column) by spliting the COA in pieces by using VAR XX and then switch statement on (sub)header level. In adventurework little more difficult than reallife because of every number in the amount-column of the general ledger is made possitive. Normally you could perform the VAR on header level. The subheaderSort is normally based on accounting-rules. Sometimes you can retreive this sort/category column already out of a system. In this case Adventurwork there is no such table so i add it manually to the COA by starting at 100 for P&L on acc level 4 (think you can try to do automatic in power query if you like). Hope this helps.

[@angelmaravilla4708]

@@controloverreports I don't understan 100% of that comment; but the 85% sounds amazing!! thks