Forum Discussion

AndreiaCoelho's avatar
AndreiaCoelho
Regular Visitor
2 years ago

How to sum a column disregarding repeated IDs

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.

1 Reply

  • ChielFaber's avatar
    ChielFaber
    Super User
    2 years ago

    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!