Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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) a...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    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.

v-juanli-msft's avatar
v-juanli-msft
Icon for Community Support rankCommunity Support
7 years ago

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.

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Thank you all for your help.  Another solution I implemented was to create 2 line charts one that shows the total number of cells with value per week and another chart that show the total number of nulls per week.