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 ,
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.
RahpNow 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 LedgersEnsure 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?