Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Small and medium business | Business Central, N... / Adjusted Profit/Commis...
Small and medium business | Business Central, N...
Suggested answer

Adjusted Profit/Commission report

(0) ShareShare
ReportReport
Posted on by kjjson

I am trying to pull the data for the commission report into Business Central. I did this through the Customer Ledger Entries table, but the profit doesn't match to the commissions report. Looking into this, it appears that it is due to the commission report being based on the Invoice Statistics Adjusted amount, but the Profit amount in the Customer Ledger Entries is different. How can I determine the adjusted profit amount? I know the invoice statistics page 397 can be set as a web service, but is there a table that has the cost, or profit percentage or something to let me calculate the profit, or can I link the customer Ledger entry table to this page web service since the 397 doesn't give much information except the stats of the invoice.

For example, I have invoice IN1000095 in the customer ledger table for sale of $99,867.32 and a profit of $28,257.45. When I run the salesperson commission report, this invoice shows for $27,931.33. I can find that on the invoice statistics report or web service I created for page 397, but not anywhere else. Any guidance/ideas?

Thanks,

Kevin

  • Suggested answer
    Inge M. Bruvik Profile Picture
    Inge M. Bruvik 993 Super User 2024 Season 1 on at
    RE: Adjusted Profit/Commission report

    The best table to use for reporting Cost and profit when it comes to item sales will in my opinion be the Item Ledger Entry table.

    There you will have a link to the corresponding sales invoice.

  • kjjson Profile Picture
    kjjson on at
    RE: Adjusted Profit/Commission report

    Thanks Daniele, what I am not sure is how to create the new data item in the web service that pulls from the invoice statistics where that is not a table, but a page. When I try and do a data item link, I don't see anything available for invoice statistics.

    Thanks,

    Kevin

  • Suggested answer
    DAnny3211 Profile Picture
    DAnny3211 9,274 Super User 2024 Season 1 on at
    RE: Adjusted Profit/Commission report

    hi

    It sounds like you are on the right track in using the Invoice Statistics Adjusted amount to calculate the commission report instead of the profit amount in the Customer Ledger Entries table. The Invoice Statistics page (397) is a good source for this information, but as you mentioned, it does not provide the cost or profit percentage.

    To determine the adjusted profit amount, you can use the following formula:

    Adjusted Profit = (Invoice Statistics Adjusted Amount) - (Direct Cost + Commission Amount)

    Here, the Direct Cost is the cost of goods sold, and the Commission Amount is the commission paid to the salesperson. You can find the Direct Cost in the Item Ledger Entries table or the Value Entries table, depending on your setup.

    To link the Customer Ledger Entries table to the Invoice Statistics web service, you can create a new data item in the web service that includes the relevant fields from both tables. Then, you can use a codeunit or report to pull data from the web service and calculate the commission report using the above formula.

    It's important to note that the commission calculation may also depend on your specific commission structure and rules. You may need to adjust the formula or include additional factors to accurately calculate the commission. It's recommended to work with a Microsoft partner or consultant who has experience with commission calculations in Business Central to ensure that you are following best practices and getting the most out of your commission report.

    DAniele

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

Congratulations 2024 Spotlight Honorees!

Kudos to all of our 2024 community stars! 🎉

Meet the Top 10 leaders for December!

Congratulations to our December super stars! 🥳

Get Started Blogging in the Community

Hosted or syndicated blogging is available! ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,622 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,354 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans