UNION table with SUMMARIZECOLUMNS with two ID types: one must have negative values

[calerof]

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

[v-piga-msft]

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

[calerof · Accepted 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

[v-piga-msft]

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