Results 1 to 5 of 5

Thread: Outstanding Orders
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jul 2013
    Posts
    49
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Outstanding Orders

    I'm trying to create a measure that shows what orders haven't been invoiced yet. I have an invoice table with both Invoice Number and Order Number and a Order Table with Order Number.

    I want to to get the value of the Orders without invoice number.

  2. #2
    Board Regular
    Join Date
    Jan 2014
    Location
    Perth, Western Australia
    Posts
    303
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Outstanding Orders

    Assuming that you can add to/modify the existing tables...

    See below for sample layout and formulas.

    1. Add a column to the Order Table labelled "Invoiced?"
    2. In this column (D8:D13) enter an IF / lookup combo formula that will determined whether the Invoice table has any order No.s entered against the invoices.
    3. Use the SUMIFS function (D3) to sum the value of orders in the Orders Table that have not been invoiced.


    A
    B
    C
    D
    E
    F
    G
    H
    2
    3
    Value of O/S orders:
    $ 875
    4
    5
    Orders Table
    Invoice Table
    6
    $ 2,025
    $ 1,150
    7
    Order No.
    $
    Invoiced?
    Invoice No.
    Order No.
    $
    8
    1001
    200
    N
    Inv 205
    1002
    255
    9
    1002
    255
    Y
    Inv 206
    1005
    420
    10
    1003
    310
    N
    Inv 207
    1006
    475
    11
    1004
    365
    N
    12
    1005
    420
    Y
    13
    1006
    475
    Y
    Sheet: .

    Key Formulas:
    D8 = IF( IFERROR( MATCH( B8, $G$8:$G$14, 0 ), 0), "Y", "N" )

    D3 = SUMIFS( $C$7:$C$13, $D$7:$D$13, "N" )
    Cheers

    Col
    Using:
    @ Home - Windows 10 and XL 2007
    @ Work - Depends on my employer (XL 2010 or 2016)

  3. #3

    Join Date
    Oct 2018
    Posts
    323
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Outstanding Orders

    Hi,

    Maybe another option:

    Sheet1

    ABCDE
    1Order Table
    2Order # Data1Data2Amount
    31 redyellow$1,000.00
    42 $1,200.00
    53 silver $5,000.00
    64 $10,000.00
    75 goldorange$900.00
    8
    9Invoice Table
    10Order #Invoice #Data1Data2Amount
    1111redyellow$1,000.00
    122 00$1,200.00
    1332silver0$5,000.00
    144 00$10,000.00
    1553goldorange$900.00
    16
    17Orders With Invoice Numbers
    18Order #Invoice #Data1Data2Amount
    1911redyellow$1,000.00
    2032silver0$5,000.00
    2153goldorange$900.00
    22
    23
    24 Total$6,900.00

    Spreadsheet Formulas
    CellFormula
    C11{=IFERROR(INDEX(C$3:C$7,MATCH($A11,$A$3:$A$7,0)),"")}
    D11{=IFERROR(INDEX(D$3:D$7,MATCH($A11,$A$3:$A$7,0)),"")}
    E11{=IFERROR(INDEX(E$3:E$7,MATCH($A11,$A$3:$A$7,0)),"")}
    C12{=IFERROR(INDEX(C$3:C$7,MATCH($A12,$A$3:$A$7,0)),"")}
    D12{=IFERROR(INDEX(D$3:D$7,MATCH($A12,$A$3:$A$7,0)),"")}
    E12{=IFERROR(INDEX(E$3:E$7,MATCH($A12,$A$3:$A$7,0)),"")}
    C13{=IFERROR(INDEX(C$3:C$7,MATCH($A13,$A$3:$A$7,0)),"")}
    D13{=IFERROR(INDEX(D$3:D$7,MATCH($A13,$A$3:$A$7,0)),"")}
    E13{=IFERROR(INDEX(E$3:E$7,MATCH($A13,$A$3:$A$7,0)),"")}
    C14{=IFERROR(INDEX(C$3:C$7,MATCH($A14,$A$3:$A$7,0)),"")}
    D14{=IFERROR(INDEX(D$3:D$7,MATCH($A14,$A$3:$A$7,0)),"")}
    E14{=IFERROR(INDEX(E$3:E$7,MATCH($A14,$A$3:$A$7,0)),"")}
    C15{=IFERROR(INDEX(C$3:C$7,MATCH($A15,$A$3:$A$7,0)),"")}
    D15{=IFERROR(INDEX(D$3:D$7,MATCH($A15,$A$3:$A$7,0)),"")}
    E15{=IFERROR(INDEX(E$3:E$7,MATCH($A15,$A$3:$A$7,0)),"")}
    A19{=IFERROR(INDEX($A$11:$A$15,MATCH($B19,$B$11:$B$15,0)),"")}
    B19{=IFERROR(INDEX($B$11:$B$15,SMALL(IF($B$11:$B$15<>"",ROW($B$11:$B$15)-ROW($11:$11)+1),ROWS($11:11))),"")}
    C19{=IFERROR(INDEX(C$11:C$15,MATCH($B19,$B$11:$B$15,0)),"")}
    D19{=IFERROR(INDEX(D$11:D$15,MATCH($B19,$B$11:$B$15,0)),"")}
    E19{=IFERROR(INDEX(E$11:E$15,MATCH($B19,$B$11:$B$15,0)),"")}
    A20{=IFERROR(INDEX($A$11:$A$15,MATCH($B20,$B$11:$B$15,0)),"")}
    B20{=IFERROR(INDEX($B$11:$B$15,SMALL(IF($B$11:$B$15<>"",ROW($B$11:$B$15)-ROW($11:$11)+1),ROWS($11:12))),"")}
    C20{=IFERROR(INDEX(C$11:C$15,MATCH($B20,$B$11:$B$15,0)),"")}
    D20{=IFERROR(INDEX(D$11:D$15,MATCH($B20,$B$11:$B$15,0)),"")}
    E20{=IFERROR(INDEX(E$11:E$15,MATCH($B20,$B$11:$B$15,0)),"")}
    A21{=IFERROR(INDEX($A$11:$A$15,MATCH($B21,$B$11:$B$15,0)),"")}
    B21{=IFERROR(INDEX($B$11:$B$15,SMALL(IF($B$11:$B$15<>"",ROW($B$11:$B$15)-ROW($11:$11)+1),ROWS($11:13))),"")}
    C21{=IFERROR(INDEX(C$11:C$15,MATCH($B21,$B$11:$B$15,0)),"")}
    D21{=IFERROR(INDEX(D$11:D$15,MATCH($B21,$B$11:$B$15,0)),"")}
    E21{=IFERROR(INDEX(E$11:E$15,MATCH($B21,$B$11:$B$15,0)),"")}
    A22{=IFERROR(INDEX($A$11:$A$15,MATCH($B22,$B$11:$B$15,0)),"")}
    B22{=IFERROR(INDEX($B$11:$B$15,SMALL(IF($B$11:$B$15<>"",ROW($B$11:$B$15)-ROW($11:$11)+1),ROWS($11:14))),"")}
    C22{=IFERROR(INDEX(C$11:C$15,MATCH($B22,$B$11:$B$15,0)),"")}
    D22{=IFERROR(INDEX(D$11:D$15,MATCH($B22,$B$11:$B$15,0)),"")}
    E22{=IFERROR(INDEX(E$11:E$15,MATCH($B22,$B$11:$B$15,0)),"")}
    A23{=IFERROR(INDEX($A$11:$A$15,MATCH($B23,$B$11:$B$15,0)),"")}
    B23{=IFERROR(INDEX($B$11:$B$15,SMALL(IF($B$11:$B$15<>"",ROW($B$11:$B$15)-ROW($11:$11)+1),ROWS($11:15))),"")}
    C23{=IFERROR(INDEX(C$11:C$15,MATCH($B23,$B$11:$B$15,0)),"")}
    D23{=IFERROR(INDEX(D$11:D$15,MATCH($B23,$B$11:$B$15,0)),"")}
    E23{=IFERROR(INDEX(E$11:E$15,MATCH($B23,$B$11:$B$15,0)),"")}
    E24=SUM(E19:E23)
    Formula Array:
    Produce enclosing
    { } by entering
    formula with CTRL+SHIFT+ENTER!


    Excel tables to the web >> Excel Jeanie HTML 4

  4. #4
    New Member
    Join Date
    Jul 2013
    Posts
    49
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Outstanding Orders

    I'm trying to do this in Power Bi. My data model looks like this.

    I'm trying to write this measure, which i'm sure is incorrect. Essentially i want a Column with Outstanding Orders and Invoiced + Outstanding Orders.

    My current attempt.
    $ Demand = CALCULATE(SUM(FctOrders[Gross Sales Orders]),CROSSFILTER(FctInvoice[ORDER_NUMBER],FctOrders[ORDER_NUMBER],BOTH))





  5. #5
    Board Regular
    Join Date
    Jan 2014
    Location
    Perth, Western Australia
    Posts
    303
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Outstanding Orders

    It would have been good to know from your OP that you were seeking a Power BI solution. I know nothing about that application so cannot offer any advice.
    Cheers

    Col
    Using:
    @ Home - Windows 10 and XL 2007
    @ Work - Depends on my employer (XL 2010 or 2016)

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •