Forum Discussion
Helper ITally Link to Powerbi
Hi Rahp ,
Thank you for your feedback. Here is an updated approach for calculating COGS and correcting the static Gross Profit and Net Profit in your P&L report.
To calculate COGS (Opening Stock + Purchases - Closing Stock):
COGS = SUM('Opening Stock'[Amount]) + SUM('Purchases'[Amount]) - SUM('Closing Stock'[Amount])Ensure your inventory tables are properly connected to your accounting tables using shared fields like VoucherID or ItemID.
Linking Inventory with TrnVoucher/Accounting Tables:
To keep COGS and inventory data accurate, set up correct relationships between inventory tables and your TrnVoucher/Accounting tables, as shown below:
Inventory Tables - TrnVoucher via VoucherID or ItemIDUse the RELATED() function in DAX to bring the necessary values into your transaction table. This will help ensure data is correctly aggregated by period and ledger.
Static Gross Profit and Net Profit:
To keep these metrics static (not drillable), create separate measures for each:Gross Profit = SUM('Sales'[Amount]) - [COGS]
Net Profit = [Gross Profit] - SUM('Expenses'[Amount])Add these measures to the Values section of your Matrix visual, and avoid placing them in the Rows to prevent drill-down. This setup will ensure your report shows static Gross Profit and Net Profit, with COGS calculated as needed.
Thanks.
Hi Rahp ,
Thanks for reaching out to the Microsoft fabric community forum.
I manually created this using my own sample data, and it worked as expected. You can give it a try using the same measures, it might be helpful.
Steps I Followed to Build P&L and Balance Sheet Drilldown in Power BI:
Imported Sample Data Using "Enter Data" Option
Manually entered tables like AccountingTransactions, LedgerMaster, and GroupTable using Power BI’s “Enter Data” feature
Created Data Model Relationships
LedgerMaster[LedgerID] - AccountingTransactions[LedgerID]
LedgerMaster[GroupID] - GroupTable[GroupID]
Created a Drilldown Hierarchy in Matrix Visual
Used GroupTable[GroupName] and LedgerMaster[LedgerName] in the Rows section of the Matrix visual.
Added the P&L Amount measure in Values.
Enabled drill-down to move from group level to ledger level.
Added a Slicer to Filter by GroupType
Used GroupTable[GroupType] as a slicer to toggle between Asset, Liability, Income, and Expense
Created a Separate Measures Table
FinancialMeasures = SELECTCOLUMNS({(1)}, "Dummy", 1)
Created Key Financial Measures
P&L Amount = SUMX('AccountingTransactions', 'AccountingTransactions'[Credit] - 'AccountingTransactions'[Debit])
BS Amount = SUMX('AccountingTransactions', 'AccountingTransactions'[Debit] - 'AccountingTransactions'[Credit])
Added Validation Measures and Displayed Them Using Card Visuals
Total Debits = SUM('AccountingTransactions'[Debit])
Total Credits = SUM('AccountingTransactions'[Credit])
Debit-Credit Difference = [Total Debits] - [Total Credits]
Displayed these as Card visuals for easy reference.
Please find the below attached .pbix file for your reference.
Regards,
Sreeteja
RahpData from SQL path is totally different
Hi Rahp ,
I understand that the data retrieved via SQL may differ in structure from the manually entered sample I provided. However, the DAX logic for calculating P&L and Balance Sheet should function correctly if the relationships and field mappings are properly configured. You can continue using the same DAX measures, such as P&L Amount = SUMX(AccountingTransactions, Credit - Debit) and BS Amount = SUMX(AccountingTransactions, Debit - Credit), provided your Ledger and Group relationships are accurately set up. Using GroupType as a slicer will also allow you to toggle between financial categories like Assets, Liabilities, Income, and Expenses. If the issue remains unresolved, please share a sample PBIX file with dummy data based on your SQL-connected structure.Hi Rahp ,
I hope the information provided above assists you in resolving the issue. If you have any additional questions or concerns, please do not hesitate to contact us. We are here to support you and will be happy to help with any further assistance you may need.