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.
Hello Community,
I have a calculated table with two types of records, invoices (INV1) and credit notes (RIN1). My problem is that the credit notes's data, i.e. Quantity and LineTotal (amount) are in positive absolute value and they need to be negative, discounting the net sales. How could I accomplish that?
Table1 = UNION( SUMMARIZECOLUMNS( INV1[OINV.CardCode], INV1[OINV.CardName], INV1[OINV.DocSubType], INV1[OINV.DocNum], INV1[OINV.DocDate], INV1[LineNum], INV1[ItemCode], INV1[Dscription], INV1[Quantity], INV1[LineTotal] ), SUMMARIZECOLUMNS( RIN1[ORIN.CardCode], RIN1[ORIN.CardName], RIN1[ORIN.DocSubType], RIN1[ORIN.DocNum], RIN1[ORIN.DocDate], RIN1[LineNum], RIN1[ItemCode], RIN1[Dscription], RIN1[Quantity], RIN1[LineTotal] ))
Thanks,
Fernando
Solved! Go to Solution.
Hello Cherry,
Thank you for your message. My background is more business analysis than technical and first time with SQL type of programming.
But I just realized yesterday that by editing the query RIN1, which is the table for credit notes and then going to columns Quantity and Amount (LineTotal), then Transform, Number Column, Multiply by -1 and that was it!
Now my SUM of net sales are correct, with negative quantities and sales (reverse sales) for credit notes.
Thanks!
Fernando
Hi @calerof,
You could have a reference of this document about how to use SUMMARIZECOLUMNS functions.
If you still need help, could you please share your data sample and your desired output,so that we can help further investigate on it?
Best Regards,
Cherry
Hello Cherry,
Thank you for your message. My background is more business analysis than technical and first time with SQL type of programming.
But I just realized yesterday that by editing the query RIN1, which is the table for credit notes and then going to columns Quantity and Amount (LineTotal), then Transform, Number Column, Multiply by -1 and that was it!
Now my SUM of net sales are correct, with negative quantities and sales (reverse sales) for credit notes.
Thanks!
Fernando
Hi @calerof,
It seems that you have solved your problem, you could accept your solution as an answer so that people who may have the same question can get the solution directly.
Best Regards,
Cherry
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
101 | |
86 | |
64 |