Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics RMS (Archived) / Reports and Average
Microsoft Dynamics RMS (Archived)

Reports and Average

(0) ShareShare
ReportReport
Posted on by Community Member Microsoft Employee

Hello !

Have anyone a solution for Margin% and average summary?

If i like to have the average on Profit Margin % in a "Active Report" i can't find out how to do that, se my example below calculated in Excel.

    QTY Price Total Total ex Vat Cost Profit Profit Marg%

Item 1 1 100         100                  80          40        40           50,00%

Item 2 1 120         120                  96          41        55           57,29%

Item 3 1 100         100                  80          42        38           47,50%

Item 4 1 200         200                160          43       117           73,13%

Item 5 1 100         100                  80          44         36           45,00%

Item 6 1 100         100                  80          45         35           43,75%

Item 7 1 300         300                240          46       194           80,83%

Item 8 1 100         100                  80          47         33           41,25%

Item 9 1 100         100                  80          48         32           40,00%

Item 10 1 100         100                  80          49         31           38,75%

Item 11 1 100         100                  80          50         30           37,50%

Item 12 1 100         100                  80          51         29           36,25%

Item 13 1 100         100                  80          52         28           35,00%

Sum:      13               1620      1296        598        698                          

48,17% Average when using "GroupMethod = groupmethodAverage"

53,86% Calculated Average based on summary rows, this one should be the right average.

Regard's

Peter Larsson

*This post is locked for comments

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Reports and Average

    Hi Peter,

    Unfortunately, this falls into a "you can't get there from here" realm.  I assume you are looking for margin % on groups such as departments and categories.

    As you noticed, the averaging in RMS simply averages the contents of the column so you can't get a proper margin percentage.  

    To achieve accurate margin, there are a few approaches, but ultimately, you need to calculate the average on the sums of sales and cost so will need to use an aggregate report.  I have pasted a sample below which calculates margin by category.  If you paste it into notepad and save as "Custom - Category Margin.qrp" in the reports directory, you should be on your way.

    //--- Report Summary --- //

    Begin ReportSummary

      ReportType = reporttypeSales

      ReportTitle = "Category Margin Report"

      PageOrientation = pageorientationPortrait

      OutLineMode = True

      Groups = 1

      GroupDescription = ""

      DisplayLogo = True

      LogoFileName = "MyLogo.bmp"

      ProcedureCall = ""

      TablesQueried = "FROM TransactionEntry WITH(NOLOCK) INNER JOIN [Transaction] WITH(NOLOCK) ON TransactionEntry.TransactionNumber = [Transaction].TransactionNumber LEFT JOIN Item  WITH(NOLOCK) ON TransactionEntry.ItemID = Item.ID LEFT JOIN Department WITH(NOLOCK) ON Item.DepartmentID = Department.ID LEFT JOIN Category WITH(NOLOCK) ON Item.CategoryID = Category.ID "

      SelCriteria = ""

      GroupBy = "Category.Name"

      SortOrder = ""

    End ReportSummary

    //--- Title Rows ---//

    Begin TitleRow

      Text = "<Store Name>"

      Font = "Arial"

      FontBold = True

      FontSize = 16

      Color = "Blue"

    End TitleRow

    Begin TitleRow

      Text = "<Report Title>"

      Font = "Arial"

      FontBold = True

      FontSize = 12

      Color = "Black"

    End TitleRow

    Begin TitleRow

      Text = "Generated On <Report Date>"

      Font = "Arial"

      FontBold = True

      FontSize = 10

      Color = "Black"

    End TitleRow

    //--- Filters ---//

    Begin Filter

      FieldName = "[Transaction].Time"

      FilterOp = reportfilteropBetween

      FilterLoLim = "<MonthStart>"

      FilterHilim = "<Today>"

    End Filter

    //--- Columns ---//

    Begin Column

      FieldName = "Category.Name"

      DrillDownFieldName = "Category.Name"

      DrillDownReportName = ""

      Title = "Category"

      VBDataType = vbString

      Formula = ""

      ColHidden = False

      ColNotDisplayable = False

      FilterDisabled = False

      ColWidth = 2070

      GroupMethod = groupmethodNone

      ColFormat = ""

    End Column

    Begin Column

      FieldName = "DName"

      DrillDownFieldName = "Department.Name"

      DrillDownReportName = ""

      Title = "Department"

      VBDataType = vbString

      Formula = "MAX(Department.Name)"

      ColHidden = True

      ColNotDisplayable = False

      FilterDisabled = False

      ColWidth = 2505

      GroupMethod = groupmethodNone

      ColFormat = ""

    End Column

    Begin Column

      FieldName = "[Transaction].Time"

      DrillDownFieldName = ""

      DrillDownReportName = ""

      Title = "Date"

      VBDataType = vbDate

      Formula = ""

      ColHidden = True

      ColNotDisplayable = True

      FilterDisabled = False

      ColWidth = 1335

      GroupMethod = groupmethodNone

      ColFormat = ""

    End Column

    Begin Column

      FieldName = "Sales"

      DrillDownFieldName = ""

      DrillDownReportName = ""

      Title = "Sales"

      VBDataType = vbCurrency

      Formula = "SUM(TransactionEntry.Price * TransactionEntry.Quantity)"

      ColHidden = False

      ColNotDisplayable = False

      FilterDisabled = False

      ColWidth = 1290

      GroupMethod = groupmethodSum

      ColFormat = ""

    End Column

    Begin Column

      FieldName = "Cost"

      DrillDownFieldName = ""

      DrillDownReportName = ""

      Title = "Cost"

      VBDataType = vbCurrency

      Formula = "SUM(TransactionEntry.Cost * TransactionEntry.Quantity)"

      ColHidden = False

      ColNotDisplayable = False

      FilterDisabled = False

      ColWidth = 1320

      GroupMethod = groupmethodSum

      ColFormat = ""

    End Column

    Begin Column

      FieldName = "Profit"

      DrillDownFieldName = ""

      DrillDownReportName = ""

      Title = "Profit"

      VBDataType = vbCurrency

      Formula = "SUM(TransactionEntry.Price * TransactionEntry.Quantity) - SUM(TransactionEntry.Cost * TransactionEntry.Quantity)"

      ColHidden = False

      ColNotDisplayable = False

      FilterDisabled = False

      ColWidth = 1380

      GroupMethod = groupmethodSum

      ColFormat = ""

    End Column

    Begin Column

      FieldName = "Margin"

      DrillDownFieldName = ""

      DrillDownReportName = ""

      Title = "Margin"

      VBDataType = vbDouble

      Formula = "(SUM(TransactionEntry.Price * TransactionEntry.Quantity) - SUM(TransactionEntry.Cost * TransactionEntry.Quantity))/(SUM(TransactionEntry.Price * TransactionEntry.Quantity))"

      ColHidden = False

      ColNotDisplayable = False

      FilterDisabled = False

      ColWidth = 1380

      GroupMethod = groupmethodAverage

      ColFormat = "#.##%"

    End Column

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

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

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

#2
Martin Dráb Profile Picture

Martin Dráb 230,235 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans