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
jonbox
Helper II
Helper II

Help on preventing count on a blank cell

Hi, I currently have the below metric which counts the number of rows containing 1 and shows as a percentage, e.g. if there are 31 rows and 16 contain 1, the measure will count that there are 31 and say 52% of the rows contain 1. 

 

My only issue with this is i don't want the measure to tally empty rows:

 

Dpt:Score:
Hair1
Beauty0
Skincare1
Products 
Face1

 

The above example should return 75% as it only counts the 1's and 0's, calculating the number of 1's from the count.

The blank cell for products is not counted. How can i reflect this in the below measure?

 

Dec % Per Dpt =
DIVIDE(
CALCULATE( COUNTROWS('Dept Scorecard 2122'), 'Dept Scorecard 2122'[Dec_6] = 1 ), 
CALCULATE( COUNTROWS('Dept Scorecard 2122'), ALL('Dept Scorecard 2122'[Dec_6] )
))
1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@jonbox , try a measure like

 


Dec % Per Dpt =
DIVIDE(
CALCULATE( COUNTROWS('Dept Scorecard 2122'), 'Dept Scorecard 2122'[Dec_6] = 1 ),
CALCULATE( COUNTROWS('Dept Scorecard 2122'), Filter(ALL('Dept Scorecard 2122'[Dec_6] ), not(isblank('Dept Scorecard 2122'[Dec_6]))
)))

View solution in original post

1 REPLY 1
amitchandak
Super User
Super User

@jonbox , try a measure like

 


Dec % Per Dpt =
DIVIDE(
CALCULATE( COUNTROWS('Dept Scorecard 2122'), 'Dept Scorecard 2122'[Dec_6] = 1 ),
CALCULATE( COUNTROWS('Dept Scorecard 2122'), Filter(ALL('Dept Scorecard 2122'[Dec_6] ), not(isblank('Dept Scorecard 2122'[Dec_6]))
)))

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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