Assistance Required for Calculated Column in Invoice Table

[InsightSeeker]

I need to create a calculated column to determine the total Invoice Amount for all sales within each invoice.

The calculated column should display the total Invoice Amount only for the first sale (as per the first unique key) in the invoice, while all subsequent sales should show 0. This ensures that the total amount is added only once per invoice.

Table 1

Unique_Key	Customer_Code	Inv_Date	Inv_No	Document	Sale_Amount
123456	123456	1-Jul-23	867558757	565787	1200.00
123457	123456	20-Jul-23	867558757	7788686	1310.00
123458	128546	3-Jul-23	778807	8768686	3210.00
123459	131091	9-Jul-23	778807	7657657	3560.00
123460	133636	16-Jul-23	779049	5699879	3910.00
123461	136181	17-Jul-23	779170	867868	4260.00

Result

Unique_Key	Customer_Code	Inv_Date	Inv_No	Document	Sale_Amount	Inv_Amount
123456	123456	1-Jul-23	867558757	565787	1200.00	2,510
123457	123456	20-Jul-23	867558757	7788686	1310.00	0
123458	128546	3-Jul-23	778807	8768686	3210.00	6,770
123459	131091	9-Jul-23	778807	7657657	3560.00	0
123460	133636	16-Jul-23	779049	5699879	3910.00	3,910
123461	136181	17-Jul-23	779170	867868	4260.00	4,260

[_AAndrade · Accepted solution]

Hi @InsightSeeker,

Here is my solution:

Inv_Amount = 
VAR CurInvNum = 'Table'[Inv_No]
VAR TotInvAmnt = 
    CALCULATE(
        SUM('Table'[Sale_Amount]),
        FILTER('Table', 'Table'[Inv_No] = CurInvNum)
    )
VAR FirstSaleCheck = 
    CALCULATE(
        MIN('Table'[Unique_Key]),
        FILTER('Table', 'Table'[Inv_No] = CurInvNum)
    )
RETURN 
    IF('Table'[Unique_Key] = FirstSaleCheck, TotInvAmnt, 0)

Final Output: