Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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
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.
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
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!
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.
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
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.
Never mind, I discovered that I need to select the measure so the formula appears, then format works!
Thanks for your help!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |