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.
Helper INow on Linking through SQL , how to layered P&L like in Tally or any other structure. Additionally , the value against the group or ledger arriving is not correct.
the linkage used :
Linked group name (mst ldger & Mst group tables)
Vocuher ID (trn accounting & trn voucher tables)
Date (Trn voucher & date table)
Ledgre UI (Trn accountign & Mst Ledger)
Hi Rahp ,
I’ve thoroughly tested everything manually.
Given that, the structure and DAX logic should function as intended, provided your relationships are set up correctly. Based on your SQL-linked structure, you can confidently cross-verify the following points:
Key Relationship Checks
Trn Accounting → Mst Ledger via LedgerID
Mst Ledger → Mst Group viaGroupID
Trn Accounting → Trn Voucher viaVoucherID
Trn Voucher → Date Table via VoucherDate
Addressing Incorrect Values in Groups or Ledgers
Ensure that the LedgerID in Trn Accounting maps correctly to the GroupID in Mst Group through Mst Ledger.
Use the same DAX logic I used -
P&L Amount = SUMX('Trn Accounting', 'Trn Accounting'[Credit] - 'Trn Accounting'[Debit])
BS Amount = SUMX('Trn Accounting', 'Trn Accounting'[Debit] - 'Trn Accounting'[Credit])
Additionally, use a slicer on GroupType from Mst Group to switch between P&L and BS.
Layering P&L Like Tally
Use a Matrix visual:
Rows: Mst Group[GroupName] - Mst Ledger[LedgerName]
Values: P&L Amount or BS Amount
Enable drill-down to replicate the layered Tally structure
- Rahp
some ledgers amount are mathcing the tally values but some are fetching wrong under incorrect grouping. Trn accounting master will have multiple line items for one sales entry like sales a/c, party a/c & Gst. So what kind of logics should be applied, after mapping the ledgers, groups & other table?
Hi Rahp ,
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.
How to provide sample data in the Power BI Forum - Microsoft Fabric Community
Thank you.
Hi Rahp ,
Kindly share sample data that fully represents your issue or question in a usable format (avoid screenshots).
Ensure that no sensitive or unrelated information is included.
Also, please provide the expected result based on the sample data you've shared.
Thank you!