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
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
Hi cmaloyb ,
You can create one measure as below:
NumberofsubA = VAR a=MAX('Table 1'[Turn-in Date (Month)])
VAR b=CALCULATE(DISTINCTCOUNT('Table 1'[Student ID]))/
CALCULATE(DISTINCTCOUNT('Table 2'[Student ID]),FILTER('Table 2',ISBLANK('Table 2'[Program Start Date])||(YEAR('Table 2'[Program Start Date])=YEAR(A)&&MONTH('Table 2'[Program Start Date])<=MONTH(a))))
return bBest Regards
Rena