View My Drafts
I have a challenge in Power BI that I need help with.I am using Power Bi to prepare P&L and Balance Sheet reports from downloaded Trial Balances. Is this something you have done before? Is it possible?
How can I go about it?
Click HERE to See what we do
Cell: +234 (0)805 801 7067
Lagos Office 1 (Training Centre)
70A Adeyemo Akapo Street
Omole Phase 1, Ikeja. Lagos.
Tel: +234 (0)700TRAINING
Tel: +234 (0)700PAYROLL
Lagos Office 2
3rd Floor, Africa Re House,
Plot 1679, Karimu Kotun Street,
Victoria Island. Lagos. Nigeria.
Tel: +234 (0)701 375 0888
17 Hanover Square
Mayfair, London. UK
Tel: +44 (0) 2075291496
7th Floor, GNAT Heights
30 Independence Avenue
Ridge, Accra, Ghana
Tel: +233 (0) 30 7010976
The way to go about this is a bit out of the box. Your report is filtering out the Gross Profit & Net Profit Lines that you want to see because the table relationships is flowing to the Fact file (GL Table) and that does not have Gross Profit, Net Profit and the likes. Follow the guides below to resolve the issues:
You will need to have the following tables:
1. Fact Table: Basically your GL account. (GL_Table).
2. A dimension table (Accounts_Table) for all accounts: More like GL Mapping. However, you need to include lines for Gross Profit, Net Profit and other aggregation lines you want to see in your P&L.
3. A dimension table (PL_Lines_Table) for all lines needed in your P&L. Sorted and sequential in order of appearance on P&L
Columns required in the tables
The tables listed above should contain the following fields:
1. GL_Table: Date; Account Name; Amounts (You should have only one values column. Use Power Query to unpivot the table. You most likely need only the closing balances so you can filter out debits and credits.
Store A revenue
Store B revenue
Admin & General
Advert & Sales
Heat, Light & Power
Repairs & Maintenance
Store A cost
Store B cost
2. Accounts_Table: Account Name; Account Category; Operator Sign for each account (e.g, revenue is +ve, cost of sales is -ve. Use 1 to represent +ve and -1 to represent -ve).
Store C revenue
Store A rent cost
Cost of sales
Store B rent cost
S & D expense
Other Indirect expenses
Profit before interest tax (PBIT)
Profit before tax (PBT)
Total operating expense
3. PL_Lines_Table: Order Number; P&L Lines; Calculation type (Use 1 for the default lines in your GL and 2 for aggregation lines you are adding in i.e Gross Profit, Net Profit etc).
Other Indirect Expenses
Profit Before Interest Tax (PBIT)
Profit Before Tax (PBT)
Total Operating Expense
You should create the following relationships between the three tables:
1. Accounts column in Account_Table and Accounts column in GL_Table
2. Account Category in Account_Table and Account Category in PL_Line_Table
You should write the following measures:
1. Amount Sum = SUM(GL_TABLE[Amount Column])
2. PL Amount = SUMX(Accounts_Table, [Amount Sum] * Accounts_Table[Sign])
3. Calculation Type = MAX(PL_Lines_Table[CalcType])
4. Running Total = IF(HASONEFILTER(PL_Lines_Table[PL Lines], CALCULATE([PL Amount], ALL(PL_Lines_Table), PL_Lines_Table[Order] < VALUES(PL_Lines_Table[Order])), BLANK())
5. Final Amount = SWITCH([Calculation Type], BLANK(), BLANK(), 1, [PL Amount], 2 [Running Total])
Explaining the measures
1. Amount Sum: This is to sum the amounts column in the fact file
2. PL Amount: This is to multiply each line item by the sign and sum row by row. So if Revenue is 100 and the sign is 1 you have 100, Cost of sales is 30 and the sign is -1, you have -30. The sum of both of which is now 70
3. Calculation Type: Get the calculation type of each line item as defined earlier. (i.e 1 for default lines and 2 for aggregation lines). This calculation is used in the final measure.
4. Running Total: The initial problem is that the gross profit, net profit and the likes get filtered out after you drop your MEASURE in the report because those lines are not in your fact table. So we calculate a running total, that: If the PL_Lines_Table gets filtered (as you would expect), DAX should do a CALCULATE of PL Amount and sum every line that is above the filtered line. Eg, if Gross profit is line 3, DAX should sum lines 1 and 2. Which is 100 and -30 to give you 70.
5. Final Amount: If calculation type (of the PL_Line_Table) is blank, return blank, if it is 1 (default lines), return the value for PL Amount, if it is 2, return the value for Running Total.
If you follow this guide, your challenge should be solved.
Good Morning Ahmed,Thank you for helping resolve the challenge I had with preparing P&L report from the Trial Balance.
Now I have a new challenge with preparing the Balance sheet. Using the SUM function aggregates the balances. I am of the opinion that a filter should be used.Kindly assist with the DAX expression to be used.
This is just perfect. Problem solved.
I really appreciate all your time and effort.