Thread: Outstanding Orders Thanks: 0 Likes: 0

1. 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.  Reply With Quote

2. 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" )  Reply With Quote

3. Re: Outstanding Orders

Hi,

Maybe another option:

Sheet1

 A B C D E 1 Order Table 2 Order # Data1 Data2 Amount 3 1 red yellow \$1,000.00 4 2 \$1,200.00 5 3 silver \$5,000.00 6 4 \$10,000.00 7 5 gold orange \$900.00 8 9 Invoice Table 10 Order # Invoice # Data1 Data2 Amount 11 1 1 red yellow \$1,000.00 12 2 0 0 \$1,200.00 13 3 2 silver 0 \$5,000.00 14 4 0 0 \$10,000.00 15 5 3 gold orange \$900.00 16 17 Orders With Invoice Numbers 18 Order # Invoice # Data1 Data2 Amount 19 1 1 red yellow \$1,000.00 20 3 2 silver 0 \$5,000.00 21 5 3 gold orange \$900.00 22 23 24 Total \$6,900.00

 Cell Formula 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  Reply With Quote

4. 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))     Reply With Quote

5. 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.   Reply With Quote

User Tag List

Tags for this Thread

invoice, number, order, orders, table  Posting Permissions

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