Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to Solution.
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.
Hi @Oscare
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.
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!
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.
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.
@Oscare you can add another column and use that for your calculation
Null or Not Null = IF ( ISBLANK( Table[MyColumn] ), "Null", "Not Null" )
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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.
@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
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@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!
Date | Test Number |
11/12/2018 | Test_03 |
11/14/2018 | Test_15 |
11/16/2018 | Test_16 |
11/18/2018 | |
11/20/2018 | Test_04 |
11/22/2018 | |
11/24/2018 | Test_01 |
11/26/2018 | Test_02 |
11/28/2018 | |
11/30/2018 | Test_08 |
12/2/2018 | |
12/4/2018 | Test_09 |
12/6/2018 | Test_10 |
12/8/2018 | Test_11 |
12/10/2018 | |
12/12/2018 | |
12/14/2018 | |
12/16/2018 | Test_14 |
12/18/2018 | |
12/20/2018 | Test_12 |
12/22/2018 | Test_13 |
12/24/2018 | |
12/26/2018 | Test_17 |
12/28/2018 | Test_18 |
12/30/2018 | Test_19 |
1/1/2019 | Test_20 |
1/3/2019 | |
1/5/2019 | |
1/7/2019 | Test_05 |
1/9/2019 | Test_06 |
1/11/2019 | Test_07 |
@Oscare so you want to show value in card visual 11 divided by 20 from this example data? correcT?
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@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 |
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |