cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper III

## 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
1 ACCEPTED SOLUTION
Super User

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:

Did I answer your question? Mark my post as a solution! Kudos are welcome.

Proud to be a Super User!

Super User

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:

Did I answer your question? Mark my post as a solution! Kudos are welcome.

Proud to be a Super User!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.