Table to show net sales

[shalnark0727]

Hi guys I need help to get net sales between two tables invoices & credit and show it in one colum in a table per client. Here's a sample simple table of what the tables look like  and the desired output. Hope you can help, thanks.

Table 1 - Invoice

Client	Date	Invoice_amount
Sam	01/10/2019	1000.00
Bob	01/14/2019	2000.00
John	01/20/2019	5000.00

Table 2 -Credit memo

Client	Date	creditmemo_amount
Paul	01/05/2019	1000.00
Bob	01/28/2019	2000.00

Desired Table to show in the dashboard

Client	Sales
Sam	1000.00
Bob	1500.00
John	5000.00
Paul	-1000.00
Total	6500.00

[Arnault_]

Hi @shalnark0727,

Here is a pbix file with your requirements (except that the total is 5.000, because for Bob net sales = 0 (2000 - 2000))

Pbix

[shalnark0727]

Thanks @Arnault_

[Arnault_]

Hi @shalnark0727,

You are welcome.

For you to better understand the proposed solution.

In your two tables you could have created a relationship and use RELATED, however, none of these tables offered this exhaustive list of your clients. It is also better practice to create a seperate table so you can later add additional information about your customers (id, address, type...)

1 / A new table is created for clients (use this table in your matrix)

2/ The relationships are made to link your tables

3/ A simple measure is created

Best,

[Arnault_ · Accepted solution]

Hi @shalnark0727,

If you create a table with the list of your clients, then you can use this measure

Net sales  = 

VAR Invoice = SUM('Table 1 - Invoice'[Invoice_amount])
VAR Credit = SUM('Table 2 -Credit memo'[creditmemo_amount])

RETURN (Invoice-Credit)

[calerof]

Hi @Arnault_ ,

Your solution doesn't accept granularity calculations, does it?, i.e. net sales by day, part number, etc.

Thanks,

Fernando

[Arnault_]

Hi @calerof ,

In theory that would be possible only if your 2 tables offer the required level of details. Of course there are alternative scenarios in that case. It depends on the data model and the expected outputs.

Best

[Arnault_]

Hi @shalnark0727,

Have you created relationship between your 2 tables?

In this scenraio, you may use SUMX and RELATED function.

There are several examples on the forum.

Update : there is another issue. You need to create a table with the list of your clients.

Regards,

[shalnark0727]

Hi @Arnault_,

Yes I created a calendar table to link the two tables and can generate the desired output for graphs, but when it comes to table i can only do this in matrix

Client	Sales	creditmemo_amount
Sam	1000.00	0.00
Bob	2000.00	500.00
John	5000.00	0.00
Paul	0.00	1000.00
Total	8000.00	1500.00

but i want it like this

Client	Sales
Sam	1000.00
Bob	1500.00
John	5000.00
Paul	-1000.00
Total	6500.00