Count duplicate values

[tborg]

I have a table with 2 columns of numbers.  I want to count the number of times any value anywhere in column 2 also occurs anywhere in column 1.  In other words, if there are 10 numbers in column 2 that are also in column 1, I would get a value of 10.

Then, if possible, I would like to get a number that represents the percentage of the duplicates to the total number of rows in column 1.  So, if there were 100 rows in column 1, this value would be 10%.

Thanks, This is very new to me, but surely there must be a simple way to do this comparison!

[Anonymous · Accepted solution]

Hi @tborg,

Thanks.

You can simply use the following pattern to format the measures (or columns):

1. Select the measure on the right in the Fields panel.

2. Navigate to the Modeling tab in the top ribbon.

3. Use  Formatting section of the Modeling tab to format the measure the way you want.

Cheers,

Pawel

[Anonymous]

Hi @tborg,

I would create two measures:

Number of Duplicates = COUNT(Table1[Column2]) - CALCULATE(
 COUNT(Table1[Column2]), 
 EXCEPT(VALUES(Table1[Column2]),VALUES(Table1[Column1]))
)

Percent of Duplicates = DIVIDE([Number of Duplicates], COUNT(Table1[Column1]))

See a sample .pbix file to check if it works for you: https://1drv.ms/u/s!AswoYNSd-msEg5MqKshT0Y2NlkMA1A.

HTH,

Pawel

[tborg]

Pawel,

Wow!  Thanks!  There is a minor difference from what I calculated in Excel, but I will search for that (some of the data in each column is not good - wrong number of digits, etc).  So now, two more questions:

1. The Number of Duplicates is 6 digits.  How do I get it to put in a comma?

2. The Percent of Duplicates shows as a decimal (0.19).  How do I express it as a percent (19%)?

Thanks again, you may have made me a hero!

[Anonymous · Accepted solution]

And one more thing. I've just realized it's safer to use COUNTROWS function in Percentage of Duplicates measure:

Percent of Duplicates = DIVIDE([Number of Duplicates], COUNTROWS(Table1))

By doing that you make yourself secure from the situations where null values appear in Column1.

[Anonymous · Accepted solution]

Hi @tborg,

Thanks.

You can simply use the following pattern to format the measures (or columns):

1. Select the measure on the right in the Fields panel.

2. Navigate to the Modeling tab in the top ribbon.

3. Use  Formatting section of the Modeling tab to format the measure the way you want.

Cheers,

Pawel

[tborg]

Pawel,

I created a separate card for each of the 2 measures.  When I select the measure and go to Modeling, the Format options are greyed out.   I changed the data type from Text to Number, but it made no difference, the format options are still not available.

Suggestions?

Thanks.

[tborg]

Never mind, I discovered that I need to select the measure so the formula appears, then format works!

Thanks for your help!