locked
creating a sum of an order in the sales order details RRS feed

  • Question

  • Hello,

    I have imported the [Sales].[SalesOrderDetail] as a query from the adventure work database into powerpivot

    now I am trying to achivement th following 2 things.

    Add a column "order total " on the same row which the sum of the line total of all lines in that order. I think I need to filter that one.

    Add a column based on that "order total" which just checks "if (order total < 25000 , "small order" , "big order")

    So far I got these two

    Measure 2:=SUMX(Query, [LineTotal])
    Measure 3:=IF([Measure 2] <25000, "Small" , "Big")

    Any idea's ?

    Constantijn

    Monday, November 25, 2013 4:07 PM

Answers

  • The first Calculated Column you want is:

    OrderTotal: = CALCULATE( SUM(SalesOrderDetail[LineTotal]), FILTER( SalesOrderDetail, SalesOrderDetail[OrderID] = EARLIER(SalesOrderDetail[OrderID])

    ) )

    Not sure if I have the actual field/table names correct but OrderID should be whatever the unique ID of each order is in the table.  SalesOrderDetail is the name of the table being used.

    Then your second column should be what you listed:

    OrderType:=
         IF([OrderTotal] < 25000,
            "Small",
            "Big"
          )

    Monday, November 25, 2013 4:18 PM
    Answerer

All replies

  • The first Calculated Column you want is:

    OrderTotal: = CALCULATE( SUM(SalesOrderDetail[LineTotal]), FILTER( SalesOrderDetail, SalesOrderDetail[OrderID] = EARLIER(SalesOrderDetail[OrderID])

    ) )

    Not sure if I have the actual field/table names correct but OrderID should be whatever the unique ID of each order is in the table.  SalesOrderDetail is the name of the table being used.

    Then your second column should be what you listed:

    OrderType:=
         IF([OrderTotal] < 25000,
            "Small",
            "Big"
          )

    Monday, November 25, 2013 4:18 PM
    Answerer
  • Excelllent, works like a charm. Thank you.

    Constantijn

    Tuesday, November 26, 2013 7:52 AM