How to sum a column disregarding repeated IDs

[AndreiaCoelho]

Hey guys!

I am having difficulty performing a sum of values. Currently, I have a database that identifies customers who canceled their subscription with us.

A single customer may have more than one reason that led them to cancel our services, so I have lines that repeat all data with the exception of this reason for leaving.

I would like to add up the total value of my churns, however, considering the loss value only once when I have it repeated.

I think I should use the churn ID column to identify if there is a repetition and disregard the sum again, but I don't know which formula to use.

[ChielFaber]

You could try to create a rownumber per group (ID CHURN) using the following usefull blogpost by Radacad:

https://radacad.com/create-row-number-for-each-group-in-power-bi-using-power-query

You would then get a column with a rownumber per ID CHURN value. 
Like so:
IDCHURN   row

22                1

22                2

23                 1

24                 1

24                 2

The last step would be to filter the Row column to value = 1 using the user interface. This would leave only one row per IDCHURN.

Another option would be to remove the column Churn reason and then click on the remove duplicates option. For a how-to see : https://excelchamps.com/power-query/remove-duplicates/

Hope this helps!