Forum Discussion
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!
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
MaggieCommunity 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
- parry2k
Super User
Anonymous you can add another column and use that for your calculation
Null or Not Null = IF ( ISBLANK( Table[MyColumn] ), "Null", "Not Null" )
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.
- parry2k
Anonymous 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
v-juanli-msftCommunity Support
Hi Anonymous
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
MaggieCommunity 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.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!
- v-juanli-msft
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
MaggieCommunity 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.
