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

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'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.