cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

Need help calculating Null and No null values in column

Hello, I'm running a report to calculate the average of null values and no null values in a specific column. How can I differentiate this in a Line Chart? Again, all the values (nulls and no nulls) are in the same column.
Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support

Re: Need help calculating Null and No null values in column

Hi @parry2k

Test with this data

First Create a calculated column

`year/weeknum = YEAR(Table1[Date])&"-"&WEEKNUM(Table1[Date],2)`

Then Create two measures

```count not null = CALCULATE(COUNT(Table1[Date]),FILTER(ALLSELECTED(Table1),Table1[year/weeknum]=MAX(Table1[year/weeknum])&&Table1[Test Number]<>BLANK()))

count null = CALCULATE(COUNT(Table1[Date]),FILTER(ALLSELECTED(Table1),Table1[year/weeknum]=MAX(Table1[year/weeknum])&&Table1[Test Number]=BLANK()))```

Add them to a line chart

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

10 REPLIES 10
Highlighted
Super User IV

Re: Need help calculating Null and No null values in column

@Oscare you can add another column and use that for your calculation

```Null or Not Null =
IF ( ISBLANK( Table[MyColumn] ), "Null", "Not Null" )```

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Highlighted
Frequent Visitor

Re: Need help calculating Null and No null values in column

Thanks for your reply @parry2k . All the cells on the new column are "Not Null" even if there's a value in the main column.

Highlighted
Super User IV

Re: Need help calculating Null and No null values in column

@Oscare not sure what you mean, if you can share sample data in excel and expected result, it will help. Read this post to get your answer quickly

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Highlighted
Frequent Visitor

Re: Need help calculating Null and No null values in column

@parry2k , Thank you for the information provided. So, let me explain what I have. I have a table with thousands of records. One of the columns is “Test Number,” which has the test information when a user provides it. If they don’t have a test, a “blank” cell is generated. I want to be able to generate a chart to calculate the number of tests created over the empty cells. In this example (see below) it would be 20 tests over 11 empty cells (no tests provided).  Thank you!

 Date Test Number 11/12/2018 Test_03 11/14/2018 Test_15 11/16/2018 Test_16 11/18/2018 11/20/2018 Test_04 11/22/2018 11/24/2018 Test_01 11/26/2018 Test_02 11/28/2018 11/30/2018 Test_08 12/2/2018 12/4/2018 Test_09 12/6/2018 Test_10 12/8/2018 Test_11 12/10/2018 12/12/2018 12/14/2018 12/16/2018 Test_14 12/18/2018 12/20/2018 Test_12 12/22/2018 Test_13 12/24/2018 12/26/2018 Test_17 12/28/2018 Test_18 12/30/2018 Test_19 1/1/2019 Test_20 1/3/2019 1/5/2019 1/7/2019 Test_05 1/9/2019 Test_06 1/11/2019 Test_07
Highlighted
Super User IV

Re: Need help calculating Null and No null values in column

@Oscare so you want to show value in card visual 11 divided by 20 from this example data? correcT?

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Highlighted
Frequent Visitor

Re: Need help calculating Null and No null values in column

@parry2k Maybe my example needs more elaboration than I thought.  There is another column called “Week.” So, I want to be able to create a chart like the one below which will show the blue line to be the number of cells with data (Test_#) by week and yellow line that indicates the number of empty cells by week. Test to No Test Ratio for week 1 would be 6/2

 Week Test Number week1 Test_03 week1 Test_15 week1 Test_16 week1 week1 Test_04 week1 week1 Test_01 week1 Test_02 week2 week2 Test_08 week2 week2 Test_09 week2 Test_10 week2 Test_11 week2 week2 week3 week3 Test_14 week3 week3 Test_12 week3 Test_13 week3 week4 Test_17 week4 Test_18 week4 Test_19 week4 Test_20 week4 week4 week4 Test_05 week4 Test_06 week4 Test_07

Test to No Test Ratio

Test to No Test Ratio

Highlighted
Community Support

Re: Need help calculating Null and No null values in column

Hi @Oscare

Please clear me how the line chart should look like?

Here is my test:

Since average for null values would get null for all rows, so i count how many rows are null.

Could you show me some example data and describe more about your expected visual?

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Highlighted
Frequent Visitor

Re: Need help calculating Null and No null values in column

Thanks! That's a great example, and I think we're getting closer to where I want @v-juanli-msft . As I mentioned earlier to @parry2k, I'm looking to generate a "Line Chart" that will have the Week# in the Axis and the Value would be the number of Tests per Week compared to the null values per week. Please see my previous example for additional details. Thanks!

Highlighted
Community Support

Re: Need help calculating Null and No null values in column

Hi @parry2k

Test with this data

First Create a calculated column

`year/weeknum = YEAR(Table1[Date])&"-"&WEEKNUM(Table1[Date],2)`

Then Create two measures

```count not null = CALCULATE(COUNT(Table1[Date]),FILTER(ALLSELECTED(Table1),Table1[year/weeknum]=MAX(Table1[year/weeknum])&&Table1[Test Number]<>BLANK()))

count null = CALCULATE(COUNT(Table1[Date]),FILTER(ALLSELECTED(Table1),Table1[year/weeknum]=MAX(Table1[year/weeknum])&&Table1[Test Number]=BLANK()))```

Add them to a line chart

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors
Top Kudoed Authors