cancel
Showing results 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

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: Hair 1 Beauty 0 Skincare 1 Products Face 1

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

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

Announcements

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

#### Power BI Monthly Update - July 2024

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

#### Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors