Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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)
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))
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,
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)
Hi @Arnault_ ,
Your solution doesn't accept granularity calculations, does it?, i.e. net sales by day, part number, etc.
Thanks,
Fernando
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
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,
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 |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
94 | |
83 | |
67 | |
59 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |