Tally 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 ItemID

Use 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.