Sum Removing Duplicates

[Soc3]

In this data set, I need the cancelled out values (the 1st and 3rd lines) to show as 0 in a column so I can only sum the total amount ($833) once. There are other columns in the table, I just need the sum per unique ID. Distinct, max and min functions don't work. How can I achieve the desired result?

Thank you in advance! 

[v-ssriganesh · Accepted solution]

Hello @Soc3,

We hope you're doing well. Could you please confirm whether your issue has been resolved or if you're still facing challenges? Your update will be valuable to the community and may assist others with similar concerns.

Thank you.

[v-ssriganesh]

Hello @Soc3,

Hope everything’s going great on your end! Just checking in has the issue been resolved, or are you still running into problems? Sharing an update can really help others facing the same thing.

Thank you.

[v-ssriganesh]

Hello @Soc3,
Thank you for reaching out to the Microsoft Fabric Forum Community.

I have reproduced your scenario in Power BI and successfully achieved the expected result ensuring that cancelling amounts (e.g., +980 and -980) are zeroed out so that only the actual valid transaction (e.g., 833) is retained and summed.

For your reference, I’ve attached the .pbix file demonstrating this solution.

If this information is helpful, please “Accept as solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.

[rohit1991]

Hi @Soc3 
You can achieve this in Power BI by creating a measure that only sums values that don’t cancel each other out (for example, when the sum per Unique ID is not zero). Here’s a direct DAX approach. Create a measure to sum only non-cancelled amounts per Unique ID:

Net Total Amount = 
VAR NetSum = SUMX(
    VALUES('YourTable'[Unique ID]), 
    CALCULATE(SUM('YourTable'[Total Amount (USD)]))
)
RETURN
IF(NetSum <> 0, NetSum, 0)

To display in your table. Use Unique ID and this new Net Total Amount measure. If you want a calculated column for row-level logic, you can mark rows as zero when they are pairs that sum to zero using grouping and logic in Power Query, but for visuals, a measure like above is best. 

[Ashish_Mathur]

Hi,

A simple drag and drop (as shown in your screenshot) should have worked.  My guess is that in the entries of the Unique ID column, the ID's have extra invisible characters.  Clean the data in that column.  Refresh All.

[MasonMA]

@Soc3 

Hi, you would need to show the entire table. With limited information I'd recommend using Power Query to transform your data a bit more again. 

In Power Query, Group by Unique ID and apply below logic to handle cancellations:
- In Power Query Editor, select your table.
- Go to 'Transform', and then 'Group By'
- Group by 'Unique ID' and aggregate 'Sum of Total Amount (USD)' with `Sum`.
- Nomally this will automatically cancel out positive/negative values for the same ID.

After transforming in Power Query, load your data in Power BI for reporting. 

Hope it helps:)