calculating percentage of errors in a column

[rwahdan]

Hi,

I have a table with the first column having data type of number but the data is type of text (in purpose). I need to calculate the percentage of error records compaired to the column and the whole table (all columns).

[sevenhills]

Power Query: If you want to do in Power Query, check this article: https://radacad.com/get-the-error-count-with-the-profiling-data-of-power-bi-data-table-using-power-q...

and using that you can do the calculation.

DAX: It is not clear you want percentage measure or have a column and percentage. Providing adding a column and percentage measure. 

Add the column:

Converted Column = IF(ISERROR( Value('Table'[Column1])), BLANK(), Value('Table'[Column1]))

Add the measure and mark it as % data type in the formatting pane:

Percentage of Errors = COUNTBLANK('Table'[Converted Column]) / COUNTROWS('Table')

 

Hope this helps!

[sevenhills · Accepted solution]

If you want just the overall rows error count in DAX, you can use this:

Add the measure and mark it as % data type in the formatting pane:

Percentage 2 = 
var _sel = COUNTROWS( 
                Filter( 
                 ADDCOLUMNS( 
                   ALL('Table'[Column1]), 
                   "Converted Column 2", 
                   IF ( ISERROR(VALUE('Table'[Column1])), BLANK(), VALUE('Table'[Column1]))
                 )
                 , [Converted Column 2] = BLANK()
                )
            )

RETURN  DIVIDE ( _sel, COUNTROWS(ALL('Table'[Column1]))  , BLANK())