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.

parry2k's avatar
parry2k
Icon for Super User rankSuper User
7 years ago

Anonymous you can add another column and use that for your calculation

 

Null or Not Null = 
IF ( ISBLANK( Table[MyColumn] ), "Null", "Not Null" )
Anonymous's avatar
Anonymous
Not applicable
7 years ago

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. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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
      • parry2k's avatar
        parry2k
        Icon for Super User rankSuper User
        7 years ago

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