Forum Discussion

tborg's avatar
tborg
Helper I
9 years ago
Solved

Count duplicate values

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!

Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    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's avatar
    Anonymous
    9 years ago

    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.

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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's avatar
      tborg
      Helper I
      9 years ago

      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's avatar
        Anonymous
        Not applicable
        9 years ago

        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