Summing Invoice Totals by Sales Order No - Many Invoices to One Sales Order No.

[hunterdelong]

Hello,

I just transitioned from an internship position to a full-time role within my organization. I was an accounting intern transitioned to financial analyst.

As an intern, one of my duties was to bill customers from an accounts receivable perspective. I was frustrated by the lack of visibility of what needed to be billed and what had already been billed. 

My overall goal is to create a report that will display the Sales Order No, the aggregate amount billed (summation of all invoices associated with the sales order No), and the remaining amount left to bill on the order. 

Is there a way to sum the invoice amounts that apply to the same Sales order No and only display the summed total of all the invoices that apply to that sales order no.? I have sales order No, Sales Order Amount, Sales invoice No, and sales invoice amount columns.

[speedramps]

Remeber we are unpaid volunteers.

Click the thumbs up and accept as solution button. 

One question per ticket please. If you need to extend your request then please raise a new ticket.

You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you !

[speedramps]

Please provide copy and paste data as tables next time, not screen shots.
Also give the table names on the data. So we can easily imput data and build solutions.

Dont rush. Please put more care and attention into your example and decsription please.

You will nede to do something like this , with correct names ....

Create DimOrder table

by appending FactSales[order] and FactInvoices[order], sort and remove duplicates.

Create 1:M relationship from DimOrder to FactsSales

Create 1:M relationship from DimOrder to FactsInvoice

Create DAX measures

OrderTotal = Sum(FactOrder[OrderAmount])
InvoiceTotal= Sim(FactInvoice[InvoicedAmount])
NotInvoicedYet = [OrderTotal] - [OrderTotal] 

The sample data you provide is not enough. 

The computer is not magic. You need to provide a better workflow. Thank you 😀

[hunterdelong]

I'm using two tables, Fact Sales_Order has many fields... but this is where I'm pulling the sales order no. and the sales order amount. The other table is Fact Sales_Invoice which is where I'm pulling the sales invoice no. and the sales invoice amount. Both of these tables have over 30 fields I could choose to include in the report....

Let me know if this is helpful or if I should provide more details.

[speedramps]

Please provide examples of input data (as tables not screen shots) 

but dont share private data.

Clearly  "I have sales order No, Sales Order Amount, Sales invoice No, and sales invoice amount columns."

is not enough info because either need somethings to link orders, invoices & payments.