locked
Count of Sales Orders RRS feed

  • Question

  • I have a database that has sales orders at a line level with multiple lines per sales order.  By using pivot table, I am able to get sales order $ due and # of lines. However, in addition I would like to be able to get a count of the number of sales orders due on that date. For example

    Sales Order        Sales Order Line      

    SO-1234              1                     

    SO-1234              2                     

    SO-1234              3                     

    SO-5678              1                     

    SO-5678              2                     

    Total                                  9

    I would like to return a count of 1 order for SO-1234 and count of 1 order for SO-5678, thus, totaling 2 orders. Is this possible in Power Pivot?

    Friday, January 20, 2012 3:46 PM

Answers

  • If you're using the CTP3 or RC0 release of PowerPivot then you can simply use

    DISTINCTCOUNT(Table1[salesOrder])

    Monday, January 23, 2012 1:03 AM
  • Assuming the name of your table is Sales.

    Define the following measures:

     

    Completed Order Line Count:

    =SUM(Sales[# of Lines Complete])

     

    Num Partially Shipped:

    =SUMX(VALUES(Sales[Sales Order]),IF([Complete Order Line Count]>0,1,0))

     

    Order Count:

    =COUNTROWS(VLAUES(Sales[Sales Order]))

     

    Ratio of Partially Shipped Orders to All Orders:

    [Num Partially Shipped] / [Order Count]

    Thursday, January 26, 2012 5:01 PM
  • How about

    =SUMX(VALUES(Table_Query_from_HVLPRDDTA[Order Number]),IF(SUM([# of Lines Complete])=0,1,0)
    Friday, January 27, 2012 3:34 AM
  • You should be able to use a calculated measure with DISTINCT like this:
    =COUNTROWS(DISTINCT(Table1[salesOrder]))

    Hope that helps.

    Brent Greenwood, MS, MCITP, CBIP
    // Please mark correct answers and helpful posts //
    http://brentgreenwood.blogspot.com
    Saturday, January 21, 2012 4:34 AM
    Answerer
  • SDTCPA -

    To expand on what ruve1k stated above, the code provided is meant to be used in a calculated measure, not a calculated column.  If you're using PowerPivot v1, the calculated measure is created from within the pivot table by right-clicking on the fact table (Sales Orders) in the field list (typically top right pane) and selecting "Add New Measure".  The calculated column is created as a column in a table within the PowerPivot window (like the back-end data view of the model).

    Hope that helps.  Please post again with results or any additional issues.  Thanks.


    Brent Greenwood, MS, MCITP, CBIP
    // Please mark correct answers and helpful posts //
    http://brentgreenwood.blogspot.com
    Tuesday, January 24, 2012 9:28 PM
    Answerer

All replies

  • You should be able to use a calculated measure with DISTINCT like this:
    =COUNTROWS(DISTINCT(Table1[salesOrder]))

    Hope that helps.

    Brent Greenwood, MS, MCITP, CBIP
    // Please mark correct answers and helpful posts //
    http://brentgreenwood.blogspot.com
    Saturday, January 21, 2012 4:34 AM
    Answerer
  • If you're using the CTP3 or RC0 release of PowerPivot then you can simply use

    DISTINCTCOUNT(Table1[salesOrder])

    Monday, January 23, 2012 1:03 AM
  • Brent,

    Thank you for your reply. Unfortunately, by adding this field, I get the same value in each raw. In my case, 5818 – meaning that there are 5818 sales orders in the database.  


    LT
    Tuesday, January 24, 2012 8:00 PM
  • The formula was not intended as a calculated column.

    You need to add it as a Measure to be used in a Pivot Table.

    Tuesday, January 24, 2012 8:02 PM
  • SDTCPA -

    To expand on what ruve1k stated above, the code provided is meant to be used in a calculated measure, not a calculated column.  If you're using PowerPivot v1, the calculated measure is created from within the pivot table by right-clicking on the fact table (Sales Orders) in the field list (typically top right pane) and selecting "Add New Measure".  The calculated column is created as a column in a table within the PowerPivot window (like the back-end data view of the model).

    Hope that helps.  Please post again with results or any additional issues.  Thanks.


    Brent Greenwood, MS, MCITP, CBIP
    // Please mark correct answers and helpful posts //
    http://brentgreenwood.blogspot.com
    Tuesday, January 24, 2012 9:28 PM
    Answerer
  • It worked! Thank you very much!!

    Now I need to capture # of orders that shipped partially meaning, that if order had 3 lines and only one shipped, I need to count it in my numerator while the denominator will show total number of orders that were due to ship that day. Example: if I had only one order due that day with multiple lines, but even if just one line shipped on that order, I need to count this order in showing 1 out of 1 (even I know that it is partial shipment on that order). Any ideas how to create this measure?

    Thank you again for your help!!

     


    LT
    Thursday, January 26, 2012 4:11 PM
  • What is the field indicating whether a line has shipped?
    Thursday, January 26, 2012 4:13 PM
  • # of Lines Complete
    LT
    Thursday, January 26, 2012 4:18 PM
  • From your original post I was under the impression that your raw data table had a separate record for each sales order line. Is there a column in that table that indicates whether that specific line has shipped?

    Note: [# of lines completed] would be an aggregated measure of those records for each sales order.

    Thursday, January 26, 2012 4:23 PM
  • Yes, my raw data table has a separate record for each sales order line. There is a column in that table [# of Lines Complete] that shows 1 if line was shipped or 0 if line did not ship.


    LT
    Thursday, January 26, 2012 4:28 PM
  • Assuming the name of your table is Sales.

    Define the following measures:

     

    Completed Order Line Count:

    =SUM(Sales[# of Lines Complete])

     

    Num Partially Shipped:

    =SUMX(VALUES(Sales[Sales Order]),IF([Complete Order Line Count]>0,1,0))

     

    Order Count:

    =COUNTROWS(VLAUES(Sales[Sales Order]))

     

    Ratio of Partially Shipped Orders to All Orders:

    [Num Partially Shipped] / [Order Count]

    Thursday, January 26, 2012 5:01 PM
  • Thank you! I adopted your logic and calculated # of orders NOT shipped via

    =SUMX(VALUES(Table_Query_from_HVLPRDDTA[Order Number]),IF([Not Completed Order Line Count]>0,1,0))

    This counts number of orders that were not shipped at all.

    If order shipped partially, I need to calculate another measure where number of orders NOT shipped will be LOWER then the above calculation because orders that were partially shipped will be subtracted. Any idea how to do that?

    Also, when I got back to my Power Pivot, I no longer can add a new measure (it's greyed out). How can I add new measures to my existing Power Table?

    Thank you a LOT for all your help!!


    LT
    Thursday, January 26, 2012 8:36 PM
  • To get this last peace of the puzzle, I need to count number of orders where for each specific order NONE of the lines have been shipped . Is this doable? Thank you!


    LT
    Friday, January 27, 2012 12:36 AM
  • How about

    =SUMX(VALUES(Table_Query_from_HVLPRDDTA[Order Number]),IF(SUM([# of Lines Complete])=0,1,0)
    Friday, January 27, 2012 3:34 AM
  • I got it! Thank you!!! The only issue that I see is that in some instances it does not subtotal correctly.

    For example, I'd like to report total count of orders where none of the lines shipped by Business Sector and Product line. However, for some reason when subtotaling, I do not see this count in some of my subtotals (please see below Schedule 1 "Summary").

     

    Summary
    Bus Sector Product Line Measure 5  
    Douglas Branded 1
    Custom 0
    Private Label 0 should be 1
    Douglas Total   0 should be 2
    Intl   0
    Intl Total   0  
    Grand Total   0 should be 2

    When I pivot in more details by Order #, those are correctly showing. Since I am reporting on a summary level, how do I fix subtotals?

    Detail by Order Number
    Bus Sector Product Line Order Number Measure 5    
    Douglas Branded SO-1234 1 ok
    Branded Total   1 ok
    Custom SO-1236 0
    SO-1237 0
    Custom Total   0    
      Private Label SO-1235 0
    SO-5680 1
    SO-5681 0
    Private Label Total   0 Not Subtotaling should be 1
    Douglas Total     0 Not Subtotaling should be 2
    Intl   SO-5678 0
    SO-5679 0
     Total   0    
    Intl Total     0    
    Grand Total     0 Not Subtotaling should be 2

    Thak you a LOT for your help!!

     


    LT
    • Marked as answer by Challen Fu Tuesday, January 31, 2012 2:50 AM
    • Unmarked as answer by SDTCPA Tuesday, January 31, 2012 2:53 PM
    Friday, January 27, 2012 7:15 PM
  • If you'd like me to take a look at your file send it to my display name at gmail

     

    Friday, January 27, 2012 7:24 PM
  • I e-mailed to you my test file. Thanks! 
    LT
    Friday, January 27, 2012 8:08 PM
  • =SUMX(VALUES(Table_Query_from_HVLPRDDTA[Order Number]),IF(SUM([# of Lines Complete])=0,1,0) works if you look at details by order number.

    However, subtotals are off as per above example. Since I report on a summary level, I do NOT see count for some orders that should be counted. Any ideas how to get subtotals and grand totals correctly?


    LT
    Tuesday, January 31, 2012 2:58 PM
  • Is this still an issue? 

    Thanks!


    Ed Price, SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Wednesday, September 18, 2013 9:12 PM