Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
rwahdan
Regular Visitor

calculating percentage of errors in a column

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).

1 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())

 

View solution in original post

2 REPLIES 2
sevenhills
Super User
Super User

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')
 
 sevenhills_0-1686164306855.png

 

Hope this helps!

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())

 

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.