Identify Errors in Table 1 with reference data from Table 2

[falsumconsult]

I am very new to data analysis and Power BI. So, please excuse me if this is an easy answer!

Background

I have payment data from a third-party with 1000 rows of payment data in 'Table 1'. Column C holds data with an 8-digit classification for the type of payment made.

'Table 2' is a small list of '8-digit' expected values for the type of payment made. 'Table 2, Col A' contains only 10 expected values.

When compared, any data point in 'Table 1, Col C' that is outside of the 'Table 2' reference data is deemed to be 'errata' with further investigation needed.

Request

I am looking to understand:

1. How I can identify rows in 'Table 1' that are deemed to be 'errata'. 
   1. Would this mean pulling these 'errata' rows into a new table?
   2. Or, am I able to do this without generating new tables?
2. Run a high-level metric which displays % of total payments with errata.

Any help & guidance would be greatly appreciated as I learn my way around Power BI and Data Analysis.

[v-jayw-msft]

Hi @falsumconsult ,

You could create two measures to get the result.

Measure1 is to add marker.

Measure1 = IF(SELECTEDVALUE(table1[columnC]) in VALUES(table2[columnA]),"not errata","errata")

Measure2 is to calculate the percentage.

Measure2 = 
var _total = calculate(sum([value]),allselected('table1'))
var _errata = calculate(sum([value]),filter(allselected('table1'),Measure1="errata"))
return
_errata/_total

Best Regards,

Jay

[falsumconsult]

Hi Jay,

This is great and a real help. The only issue I have is that measure 2 is returning a string value. Can you please advise on how I convert to a %?

[v-jayw-msft]

Hi @falsumconsult ,

What's data type of the [values]? _total  and _errata sum of corresponding value. They should be number type.

Try modifying '_errata/_total' to divide(_errata,_total).

If you are saying the format, you could select the measure, go to Data View, modelling Tab, Change the data type to percentage.

Best Regards,

Jay

[johnt75]

Create a 1-to-many relationship from Table2 Column A to Table 1 Column C. Put the data columns you need from Table 1 into a table visual and apply a filter so that you only show values where Table 2 Column A is blank.

[jaideepnema]

Hi @falsumconsult ,

There are many ways of doing this. You can import both the tables into Power BI and create a relationship basis the 8 digit key which as per understanding will create a many to one relationship with the table 1 on the many side.  Then you can use DAX Measures to calculate the percentage of total payments with errata. You will get a blank value whenever a matching value is not found.

Alternatively you can merge these two tables into Power BI Query editor to get the required column into your 1st table and then write measures to the final calculation required. Hope this helps !!

Please accept this as a solution if your question has been answered !!

Appreciate a Kudos 😀

Connect with me on LinkedIn: https://www.linkedin.com/in/jaideepnema/