Percentage of Non-Blank Columns

Question

I have a data set where there are multiple nullable columns and a RegistrationStatusId column (foreign key). What I'm trying to do in Power Bi is display the percentage of non-blank values for the Birthday column in a Matrix.

The idea is that I want to display the percentage of values that are not blank (i.e. null) for a given RegistrationStatusId. For example, if there are 100 rows with a RegistrationStatusId of 1, 10 of those rows have blank Birthday columns, and 3 of those rows have blank LastName columns, then I would want the birthday column in the matrix to display 90% and 97%:

+---+----------+-----------+
|   | Birthday | Last Name |
+---+----------+-----------+
| 1 |    90%   |    97%    |
+---+----------+-----------+

Whenever I go to setup my matrix, I'm immediately running into issues. I set the Rows property to the referenced column Name that corresponds to the RegistrationStatusId, so far so good. I then set the Values property to represent the Count of the Birthday column. This displays the count of all Birthdays for the respective RegistrationStatus. I change the filter so that it shows items when the value is not blank and change the value to be displayed as a percentage of the column total. But this calculates the total based on the filtered rows (not blank) that are displayed in the matrix.

So to fully ask my question. How do I display the percentage of non-blank values in a column in a matrix?

David David · Accepted Answer · 2019-08-06T21:55:11

I was able to answer my own question by implementing the following Measure:

PercentageOfNonBlankBirthdays = (
    COUNT(Resources[BirthDate]) / (COUNT(Resources[BirthDate]) + COUNTBLANK(Resources[BirthDate])) * 100
)

Update as per Jelle Hoekstra's suggestion to simplify the expression:

PercentageOfNonBlankBirthdays = (
    COUNT(Resources[BirthDate]) / COUNTROWS(Resources) * 100
)

Percentage of Non-Blank Columns

1 Answers