web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Small and medium business | Business Central, N... / How to find or link Sa...
Small and medium business | Business Central, N...
Suggested Answer

How to find or link Sales and Cost data in Dynamics 365 BC for reporting

(8) ShareShare
ReportReport
Posted on by MA-08090702-0 33

Hi everyone,

I’m working on a Power BI report connected to Dynamics 365 Business Central, and I need to calculate Profit Margin.

The challenge I’m facing is:

  • I can’t find a single table that contains both Sales Amount and Cost Amount.
  • I also couldn’t identify two tables with a clear common field (like SO No, PO No. or Item No.) to create a reliable relationship.

Could someone please guide me on:

  • Which table(s) in BC should I use to get both Sales and Cost data?
  • If they are in separate tables, what’s the standard way to link them so I can calculate profit margin in Power BI?
 

    Thanks!

Categories:
Dynamics 365 Business Central
I have the same question (0)
  • André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 305,182 Super User 2026 Season 1 on at
    Moved from "Engage with the community" to the Dynamics 365 Business Central forum.
  • Gerardo Rentería García Profile Picture
    Gerardo Rentería Ga... 26,834 Most Valuable Professional on at
  • Suggested answer
    YUN ZHU Profile Picture
    YUN ZHU 101,250 Super User 2026 Season 1 on at
    If you don't associate SO and PO (Special Order, Drop Shipment...), or if you don't use Item Tracking Lines (such as Lot No.), I don't think it will be possible to find the accurate associated data.
     
    Hope this can give you some hints.
    Thanks.
    ZHU
  • MA-08090702-0 Profile Picture
    MA-08090702-0 33 on at

    Hi everyone,

    Highly appreciate your response to my earlier question 🙏. I realized that we were not using the Item Tracking feature in Dynamics 365 Business Central.

    Could someone please guide me on:

    • How to turn on or enable Item Tracking?
    • Do I need to configure it at the Item Card level  or is there a system-wide setup?
    • Are there any considerations I should be aware of before enabling it (impact on transactions, reporting, or performance)?

    Thanks again for your help!

     
  • Suggested answer
    Suresh Kulla Profile Picture
    Suresh Kulla 50,278 Super User 2026 Season 1 on at
    The table you need to check is the Item Ledger Entry and GL Entry table for the amount or Cost, to find the related SO and PO. If you have the archive enabled, then you see the Purch. Order No. and Line No. on the Sales Line Archive Table, by finding the order no. from the Ledger Entry Table.
  • Suggested answer
    YUN ZHU Profile Picture
    YUN ZHU 101,250 Super User 2026 Season 1 on at
    Hi, hope the following can give you some hints.
    Track items with serial, lot, and package numbers
     
    However, this function cannot be enabled for items that have already been traded.
     
    Thanks.
    ZHU
  • Gerardo Rentería García Profile Picture
    Gerardo Rentería Ga... 26,834 Most Valuable Professional on at
  • Suggested answer
    Mansi Soni Profile Picture
    Mansi Soni 9,611 Super User 2026 Season 1 on at
    Hello @MA-08090702-0,

    In Business Central, Sales Amount is in the Sales Invoice Line table and Cost Amount is typically in the Item Ledger Entry or Value Entry tables. Since there’s no single table with both, the standard approach in Power BI is to link them via the Item No. and Posting Date (and optionally Document No. if available) to match sales transactions to their corresponding costs. This allows you to create a measure for Profit Margin = (Sales Amount – Cost Amount) / Sales Amount even though the data comes from separate tables.

    Hope this answer will help you as well!

    Regards,
    Mansi Soni
  • Suggested answer
    Sumit Singh Profile Picture
    Sumit Singh 11,795 Super User 2026 Season 1 on at
    Hi, 
     
    ·  Create a Data Model in Power BI:
    • Sales Amount → from Sales Invoice Line.
    • Cost Amount → from Value Entry (or Item Ledger Entry if needed).
    ·  Relationships:
    • Link on Item No. and Document No. (and Posting Date if needed).
    • Use Entry Type = Sale in Value Entry for cost.
    Mark true, “Does this answer your question” if it helps.
     
  • Suggested answer
    Valentin Castravet Profile Picture
    Valentin Castravet 32,693 Super User 2026 Season 1 on at
    If you’re buying or selling items, the Item Ledger Entry table contains the information you need. Each time you sell an item, the system automatically links it to a purchase. You can review the Sales Amount (Actual) and Cost Amount (Actual) fields in this table to get the details.
     

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Introducing the 2026 Season 1 community Super Users

Congratulations to our 2026 Super Stars!

Meet the Microsoft Dynamics 365 Contact Center Champions

We are thrilled to have these Champions in our Community!

Congratulations to the April Top 10 Community Leaders

These are the community rock stars!

Leaderboard > Small and medium business | Business Central, NAV, RMS

#1
OussamaSabbouh Profile Picture

OussamaSabbouh 2,140 Super User 2026 Season 1

#2
YUN ZHU Profile Picture

YUN ZHU 1,362 Super User 2026 Season 1

#3
AndrewThomas81 Profile Picture

AndrewThomas81 1,216

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans