"Total" Count of Duplicates

[DRing]

I am looking for a way to count the total numnber of duplicate records on a column. Using the sample data below for duplicate "Number", the expected output would be 6 =  4 for 001 + 2 for 003.

 

[vicky_ · Accepted solution]

Try this formula:

Number of Duplicates = 
var summaryTable = ADDCOLUMNS(VALUES('Table'[Column1]), "duplicates", CALCULATE(COUNT('Table'[Column1])))
return SUMX(FILTER(summaryTable, [duplicates] > 1), [duplicates])

[vicky_ · Accepted solution]

Try this formula:

Number of Duplicates = 
var summaryTable = ADDCOLUMNS(VALUES('Table'[Column1]), "duplicates", CALCULATE(COUNT('Table'[Column1])))
return SUMX(FILTER(summaryTable, [duplicates] > 1), [duplicates])

[DRing]

@vicky_ Do you know how I can adjust this so that it returns "0" instead of "Blank" if the result is no duplicates?

[sevenhills]

Use COALESCE ...

Say, like this 

Count Dups only Measure 3 = COALESCE( SUMX( Filter( GROUPBY('Table', [Number], "Dups Cnt", COUNTX( CURRENTGROUP(), 1)), [Dups Cnt] > 1), [Dups Cnt]) , 0)

[DRing]

Worked perfectly. Thanks!

[sevenhills]

First of all your expected output is wrong! It should be 7

You can achieve this as two measure approach as below. hope it helps! 

First measure to count 

Cnts = count('Table'[Number])

Second measure to count duplicates

Count Dups only Measure = SUMX( FILTER ( DISTINCT ( 'Table'[Number] ), [Cnts] > 1 ), [Cnts] )

Sample output:

[DRing]

You're absolutely right. It should be 7. 🙄

Thanks!

[sevenhills · Accepted solution]

If you want everything in one single measure, you can do as

Count Dups only Measure = SUMX( Filter( GROUPBY('Table', [Number], "Dups Cnt", COUNTX( CURRENTGROUP(), 1)), [Dups Cnt] > 1), [Dups Cnt]) 

You get the same output. I prefer two measure approach, as we can use "Cnts" for other places if needed.

🙂

[DRing]

Makes sense. Thanks!