Forum Discussion
Helper IILine Graph with Multiple Months and Averages
Hi cmaloyb ,
Please try to update the formula of measure as below:
NumberofsubA = VAR a=MAX('Table 1'[Turn-in Date (Month)]) VAR b=DIVIDE(CALCULATE(DISTINCTCOUNT('Table 1'[Student ID])), COUNTBLANK('Table 2'[Program Start Date])+CALCULATE(COUNT('Table 2'[Student ID]),FILTER('Table 2',NOT(ISBLANK('Table 2'[Program Start Date]))&&'Table 2'[Program Start Date]<=a)),0) return bHere updated the formula of calculating the number of active students:
1. Calculate the number of program start date which is blank
COUNTBLANK('Table 2'[Program Start Date])2. Calculate the number of program start data which is less than assignment submit date
CALCULATE(COUNT('Table 2'[Student ID]),FILTER('Table 2',NOT(ISBLANK('Table 2'[Program Start Date]))&&'Table 2'[Program Start Date]<=a)3. Add both to get active student numbers
Best Regards
Rena
Hi cmaloyb ,
If I understand correctly , you have two tables : Table 1 and Table 2 . Table 1 contains the student ID and the date which the student submit assignment , and Table 2 contains the student and the date the program was started . It looks like as below:
Your expected result is to get one line chart as below screenshot with data in X axis and the percentage of active students number and the submitted assignments display in "Values" field?
Whether the above understanding is correct? If no, please correct me. And could you please provide some sample data (exclude sensitive data) from this 2 tables with text?
Best Regards
Rena
Helper IIHi Anonymous, that is correct.
Table 1 will have multiple submissions from more than one student whereas Table 2 will only have the student and their respective program start date once (one-to-many relationship). I will provide some example data (non-sensitive) of both Table 1 and 2.
Table 1:
| Unique ID | Student | Student ID | Turn-in Date (Month) |
| T0001 | A | S0001 | 1/3/2019 |
| T0002 | B | S0002 | 1/3/2019 |
| T0003 | C | S0003 | 1/2/2019 |
| T0004 | D | S0004 | 1/6/2019 |
| T0005 | E | S0005 | 1/23/2019 |
| T0006 | F | S0006 | 1/15/2019 |
| T0007 | G | S0007 | 1/9/2019 |
| T0008 | A | S0001 | 2/16/2019 |
| T0009 | D | S0004 | 2/14/2019 |
| T0010 | E | S0005 | 2/28/2019 |
| T0011 | B | S0002 | 2/1/2019 |
| T0012 | G | S0007 | 2/2/2019 |
| T0013 | A | S0001 | 3/1/2019 |
| T0014 | B | S0002 | 3/1/2019 |
| T0015 | C | S0003 | 3/2/2019 |
| T0016 | F | S0006 | 3/3/2019 |
| T0017 | A | S0001 | 4/1/2019 |
| T0018 | B | S0002 | 4/16/2019 |
| T0019 | D | S0004 | 4/20/2019 |
| T0020 | E | S0005 | 4/20/2019 |
| T0021 | F | S0006 | 4/21/2019 |
| T0022 | G | S0007 | 4/22/2019 |
| T0023 | A | S0001 | 5/1/2019 |
| T0024 | B | S0002 | 5/2/2019 |
| T0025 | C | S0003 | 5/3/2019 |
| T0026 | D | S0004 | 5/4/2019 |
| T0027 | E | S0005 | 5/5/2019 |
| T0028 | F | S0006 | 5/6/2019 |
| T0029 | G | S0007 | 5/7/2019 |
| T0030 | B | S0002 | 6/24/2019 |
| T0031 | C | S0003 | 6/28/2019 |
| T0032 | E | S0005 | 6/29/2019 |
| T0033 | G | S0007 | 6/30/2019 |
| T0034 | A | S0001 | 7/12/2019 |
| T0035 | C | S0003 | 7/22/2019 |
| T0036 | G | S0007 | 7/29/2019 |
| T0037 | A | S0001 | 8/12/2019 |
| T0038 | B | S0002 | 8/13/2019 |
| T0039 | C | S0003 | 8/14/2019 |
| T0040 | D | S0004 | 8/15/2019 |
| T0041 | F | S0006 | 8/16/2019 |
Extends to December but didn't seem necessary to provide submissions for all months.
Table 2: (Blanks represent previous start dates prior to 2019)
| Student ID | Program Start Date |
| S0001 | |
| S0002 | |
| S0003 | 3/1/2019 |
| S0004 | |
| S0005 | 4/1/2019 |
| S0006 | |
| S0007 | 10/1/2019 |
As for the line chart, you are absolutely correct. I would like to display each month in the x-axis and in the "Values" field I would like to have the average of submissions per active students on a monthly basis.
For example, in January, I would like to display the value: (7 Submissions)/(4 Active Students) = 1.75 or 175%
So a value for each month on one continuous line graph.
Note: I do need the query as shown above for other graphing purposes.
Thank you for your response!
R/
Connor