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 IThe results are now appearing correctly, but my current concern is configuring the Cost of Goods Sold (COGS) in the Profit & Loss (P&L) section as per the given hierarchy. Additionally, when publishing the dashboard, it's prompting for a data gateway—I need to understand how to set this up properly.
Hi Rahp ,
Configure COGS in P<o accurately display COGS in the Profit & Loss hierarchy (similar to Tally), follow these steps:
Identify ledgers or groups classified as COGS, such as Purchase or Direct Expenses.
In your Mst Group or Mst Ledger, make sure these items have a clear identifier, like GroupType = "COGS" or a custom column.
Update your P&L measure
COGS Amount =
CALCULATE(
SUMX('Trn Accounting', 'Trn Accounting'[Debit] - 'Trn Accounting'[Credit]),
'Mst Group'[GroupType] = "COGS"
)
Apply this measure in the P&L Matrix visual, placing it below the main group as needed.
Adjust visual filters or slicers to position COGS between Income and Gross Profit if required.
Data Gateway for SQL Connection (On-Premises)If you’re connecting to SQL data and publishing to Power BI Service:
Install the On-premises data gateway on a machine that remains on.
During installation:
Sign in with the same Microsoft account used for Power BI Service.
Select Standard Mode (recommended for Power BI).
After publishing your report:
Navigate to Power BI Service - Settings - Dataset - Gateway Connection.
Map your data source credentials (like SQL Server name, database name, authentication method).
Confirm the status is “Online”.With these steps, scheduled refreshes and live queries should function without errors.
- Rahp
In SQL data of tally there is no different group type as COGS , its a calculated logic, so need guidance regarding
COGS= Opening Stock+Purchases -Closing Stock
This should refelct below sales with the layer lke COGS (Opening Stock+Purchases-Closing Stock) & under each group i.e opening stock > its ledgers should reflect like Stock of RM, Pkg, etc.
Main issue:
How Inventory tables should be linked with the trnvoucher/accounting tables for the period wise COGS Parameters.
And if Linked the layers need to be displayed will not work as the matrix (rows) has elements of display name, Group name, Ledger names.
Additionally , the Gross Profit & Net Profit should be static not in drill down form as shown here.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.