Help with formula for OCR testing

[antontirol]

Hello, I have table like this:

Column1	ID
15	1
15	2
21	1
15	2
Text1	1
Text1	2

I want to compare column 1 value if ID is 1 and 2 and then get how many % of rows are same.
In this case result should be 66,6%.

It should be in DAX.I have about 70 columns which i want to compare on ID 1 and 2, so i cant use Custom column with power M for  this. There will be huge numbers of columns.

Can you help me with this please?

[v-zhenbw-msft · Accepted solution]

Hi @antontirol ,

We can do some transformations in Power Query Editor and create a measure to meet your requirement.

1. We need to get the rank in each group. So we need to group the ID and add an index column in it.

2. Then we can copy the same table, one filters the ID to 1, another filters the ID to 2.

3. At last we need to merge table and table 2, then add a conditional column.

4. Then we need to create a measure to get the result.

Measure = DIVIDE(SUM('Table'[Custom]),COUNTROWS('Table'))

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

BTW, pbix as attached.

[amitchandak]

@antontirol , Try a measure like

divide(count(Table[ID]) - distinctcount(Table[ID]),count(Table[ID]) )

[antontirol]

Hello @amitchandak 

Thank you for your solution. I should wrote it before, there are also some additionals column in the table which i don't want to compare, how should i FILTER your fomula only to apply for some or make exception for these which i don't want to compare?

[AllisonKennedy]

Power BI will need more information to know that it should compare Text1 to Text1 and not compare Text1 to 15. Is there any column in the table that is the same (transaction ID or something similar?) that can tell Power BI which two rows to compare? Otherwise you will need to add this column - perhaps as an index in Power Query?

[antontirol]

Hello  @AllisonKennedy ,

I dont have index column but can sure make new. 
I would like to describe more what im trying to achieve.

It is project for comparing text read by OCR from documents.
I have 2 tables which i merged together.

In one table are document as it should be from from pdf.
In second are data from OCR, there are many test on same documents with different settings. ID is different for different OCR settings. 

This report should compare data from OCR and original data and show how many values are correct. Goal is to find which OCR setting works best.

[AllisonKennedy]

Can you share please a more accurate format of your raw data, before you merged the tables together? I think you can get the result you're looking for by changing the way you have done the merge. If possible, please share a sample file via OneDrive link or similar with sample data and confidential information removed.