cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Oscare Frequent Visitor
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
Community Support Team
Community Support Team

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

Hi @parry2k 

Test with this data

9.png

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

8.png

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.

View solution in original post

10 REPLIES 10
Super User
Super User

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





Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos Smiley Happy
Feel free to email me with any of your BI needs.





Oscare Frequent Visitor
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. 

Super User
Super User

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

 

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos Smiley Happy
Feel free to email me with any of your BI needs.





Oscare Frequent Visitor
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!

 

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

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?






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos Smiley Happy
Feel free to email me with any of your BI needs.





Oscare Frequent Visitor
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

 

Example.jpgTest to No Test Ratio


Test to No Test Ratio

 

 

Community Support Team
Community Support Team

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:

1.png

 

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.

Oscare Frequent Visitor
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!

Community Support Team
Community Support Team

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

Hi @parry2k 

Test with this data

9.png

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

8.png

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.

View solution in original post

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 53 members 1,122 guests
Please welcome our newest community members: