Tally Link to Powerbi

[Rahp]

Want to perform Drill down view of P&L & balance sheet in Powerbi. Connected the Tally with data through SQL , but not able to construct the P&L Properly as value fetched in accordance with the gorups are not correct. Need detail guidance how to construct the P&L & BS with correct figures. 
The table taken into consideration are accounting trn, group table, voucher table , ledger master table 

[Rahp]

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

---

[v-sshirivolu]

Hi @Rahp ,
Configure COGS in P&L

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

[v-sshirivolu · Accepted solution]

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.

[v-sshirivolu]

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

[Rahp]

Data from SQL path is totally different 

[v-sshirivolu]

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.

[v-sshirivolu]

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.

[Rahp]

Now 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)

[v-sshirivolu]

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? 

[v-sshirivolu]

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.

[v-sshirivolu]

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!

[v-sshirivolu]

Hi @Rahp ,

Please share sample data that clearly shows your issue or question in a usable format (not screenshots).

Make sure it doesn't include sensitive or unrelated details.

Include the expected result based on your sample data.