cancel
Showing results for 
Search instead for 
Did you mean: 
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
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power BI Dev Camp Session 27

Ted's Dev Camp

This session walks through creating a new Azure AD B2C tenant and configuring it with user flows and custom policies.