How to return 0% if blank in KPI visual

[Anonymous]

Hello everyone,

As a beginner at PowerBI, I would appreciate any help.

I am using the KPI visual to display a percentage. The issue is that when I use a filter on my dashboard to look at the KPIs for a specific segment, if a KPI only contains "null", it returns 100% (instead of 0%). I believe this is because it is counting all of the cells as a value when they are all null. The KPI visual works just fine if there is at least one non-null value and displays the correct percentage.

The column from which I'm pulling the data either has "1" or "null", and I want to calculate the count (or sum) of the "1" values over all of the column values. Below are the specific displays I would want to see (values/column total %).

Example 1: Display 40%

Column

1

null

null

1

null

Example 2: Display 0%

Column

null

null

null

null

null

Keep in mind that in terms of visual, I only want to see the percentage as follow (not in a table):

Basically, would anyone know how I can display a percentage as 0% when all values are null?

Thank you!

[v-yanjiang-msft · Accepted solution]

Hi @Anonymous ,

Based on your description, I wonder the null value in your sample is text "null" or blank null.

In Power BI Desktop data view, please check if the blank cells display blank like below:

If not, if there is "null" text display, it prove they are actually not blank, then you should replace "null" will blank in Power Query.

I create a common sample and the code works fine.

Measure1 =
DIVIDE ( COUNTA ( 'Table'[Column1] ), COUNTROWS ( 'Table' ) )

Measure2 =
DIVIDE ( COUNTA ( 'Table'[Column2] ) + 0, COUNTROWS ( 'Table' ) )

Result:

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hello,

So I tried the exact same code with a different column, but I'm now getting this error message: 

Any clue as to why? I tried changing the value type to numbers, but it didn't work... Thanks!

[aj1973]

@Anonymous 

Maybe add 0 or BLANK()

DIVIDE ( COUNTA ( 'Table'[Column2] ) + 0, COUNTROWS ( 'Table' ), 0 )

[Anonymous]

Hi Kalyj,

Thank you so much! The code worked 🙂

[aj1973]

Hi @Anonymous 

To your measure add " + 0 "

[Anonymous]

Hi Armine,

I tried adding a 0 in the measure, but it doesn't seem to work. 

My measure looks like this:

Count = COUNTA (table[column])+0

[aj1973]

Please try this

Count = IF(ISBLANK(COUNTA (table[column])), 0, COUNTA (table[column])