Outstanding Orders

[tqn626]

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.

 

[Col Delane]

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.

[TABLE="class: head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[TD]

H​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]

2 ​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]

3 ​

[/TD]
[TD]Value of O/S orders:[/TD]
[TD][/TD]
[TD]

$ 875
​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]

4 ​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]

5 ​

[/TD]
[TD]

Orders Table​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

Invoice Table​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]

6 ​

[/TD]
[TD][/TD]
[TD]

$ 2,025 ​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

$ 1,150 ​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]

7 ​

[/TD]
[TD]

Order No.​

[/TD]
[TD]

$​

[/TD]
[TD]

Invoiced?​

[/TD]
[TD][/TD]
[TD]

Invoice No.​

[/TD]
[TD]

Order No.​

[/TD]
[TD]

$​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]

8 ​

[/TD]
[TD]

1001​

[/TD]
[TD]

200
​

[/TD]
[TD]

N​

[/TD]
[TD][/TD]
[TD]

Inv 205
​

[/TD]
[TD]

1002​

[/TD]
[TD]

255​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]

9 ​

[/TD]
[TD]

1002​

[/TD]
[TD]

255
​

[/TD]
[TD]

Y​

[/TD]
[TD][/TD]
[TD]

Inv 206​

[/TD]
[TD]

1005​

[/TD]
[TD]

420​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]

10 ​

[/TD]
[TD]

1003​

[/TD]
[TD]

310 ​

[/TD]
[TD]

N​

[/TD]
[TD][/TD]
[TD]

Inv 207​

[/TD]
[TD]

1006​

[/TD]
[TD]

475​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]

11 ​

[/TD]
[TD]

1004​

[/TD]
[TD]

365 ​

[/TD]
[TD]

N​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]

12 ​

[/TD]
[TD]

1005​

[/TD]
[TD]

420 ​

[/TD]
[TD]

Y​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]

13 ​

[/TD]
[TD]

1006​

[/TD]
[TD]

475 ​

[/TD]
[TD]

Y​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: .[/TD]
[/TR]
</tbody>[/TABLE]

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" )

 

[Legacy 436357]

Hi,

Maybe another option:

Excel Workbook
	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
Sheet1

 

[tqn626]

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))

 

[Col Delane]

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.