Forum Discussion
Solved
Assistance Required for Calculated Column in Invoice Table
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 |
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:
1 Reply
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: