Forum Discussion

antontirol's avatar
antontirol
New Member
6 years ago
Solved

Help with formula for OCR testing

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 a...
Need Help
Tutorial Requests
  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    6 years ago

    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's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

antontirol , Try a measure like

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

antontirol's avatar
antontirol
New Member
6 years ago

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?