Calculated Column

[InsightSeeker]

I need to create a calculated column to determine the total Paid Amount.

From the Payment table, sum the total paid amount for each Invoice (Inv Number) and create a calculated column in the Sale_1 table.

The calculated column should display the total paid amount only for the first sale of the invoice in the Sale_1 table (based on the first unique key). All subsequent sales should show a paid amount of 0.

Attached is the PBIX file for reference. Click Here

Result (Sales_1 Table)

Inv_Date	Inv_No	Document	Sale_Amount	Inv_Amount	Paid_Amount
1-Jul-23	867558757	565787	1200.00	2,510	2,510
20-Jul-23	867558757	7788686	1310.00	-	-
3-Jul-23	778807	8768686	3210.00	6,770	3,210
9-Jul-23	778807	7657657	3560.00	-	-
16-Jul-23	779049	5699879	3910.00	3,910	3,910
17-Jul-23	779170	867868	4260.00	4,260	4,260

[_AAndrade · Accepted solution]

Hi @InsightSeeker,

I'm attaching a pbix file with my solution.

[Arul]

try this,

Sales = 
VAR _curInv = 'Table (2)'[Inv_No]
VAR _curDate = 'Table (2)'[Inv_Date]
VAR _paid = CALCULATE(
    MAX('Table (2)'[Sale_Amount]),
    FILTER('Table (2)','Table (2)'[Inv_Date] = _curDate))
VAR _key = CALCULATE(
    MIN('Table (2)'[Unique_Key]),
    FILTER('Table (2)','Table (2)'[Inv_No] = _curInv))
RETURN IF('Table (2)'[Unique_Key] = _key,_paid,0)

[InsightSeeker]

Hi @Arul  - From the Payment table, I need to sum the total paid amount for each Invoice (Inv Number) and create a calculated column in the Sale_1 table. The suggestion which you gave doesnot calcuilate the paid amount from the payment table.