locked
How to DISTINCT SUM a column based on other columns in my SQL query in PowerPivot ?(Excel 2013) RRS feed

  • Question

  • Hello,
    Here is the example,

    OrderNo            Date              Component       QtyRequired       QtyUsed
         3            2017-10-18             A301                    20                 18
         3            2017-10-19             A301                    31                 32
         3            2017-10-20             A301                    12                 15
        16           2017-10-19             D180                    44                 40
        16           2017-10-20             D180                    16                 21
         8            2017-10-18             F002                     5                   6
        11           2017-10-19             D180                    53                 55

    I want to aggregate my table into data like this :

    OrderNo       Component         QtyRequired           QtyUsed
         3                 A301                     63                      65
        16                D180                     60                      61
         8                 F002                      5                        6
        11                D180                     53                      55

    Is it possible to keep only one record based on OrderNo and Component and sum up the quantity from all rows which contains the same value of OrderNo and Component ?

    Monday, October 23, 2017 10:01 AM

Answers

  • Possible

    use a query as below for the source which extracts data to powerpivot

    SELECT OrderNo,Component,SUM(QtyRequired) AS QtyRequired,SUM(QtyUsed) AS QtyUsed
    FROM TableName
    GROUP BY OrderNo,Component


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by wilsmalin Monday, October 23, 2017 1:42 PM
    Monday, October 23, 2017 10:56 AM

All replies

  • Possible

    use a query as below for the source which extracts data to powerpivot

    SELECT OrderNo,Component,SUM(QtyRequired) AS QtyRequired,SUM(QtyUsed) AS QtyUsed
    FROM TableName
    GROUP BY OrderNo,Component


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by wilsmalin Monday, October 23, 2017 1:42 PM
    Monday, October 23, 2017 10:56 AM
  • Thanks a lot Visakh
    It works perfectly !
    Monday, October 23, 2017 1:43 PM