Line Graph with Multiple Months and Averages

[cmaloyb]

Hi there,

I am fairly new to Power BI and need help with creating a trend line/line graph from my data.

My first table, Monthly Turn-In Table tracks when students A through G turn in a monthly assignment. The fields in this table are Unique ID which is self explanatory, Student (A through G), associted ID, and date on when assignment was turned in. 

The second table, ActiveGrade, gives a date (or null) for students that have been in the program long enough that their grade will begin to count as credit. For example, Student C has been turning in their monthly assingment since they began the program but come 3/1/2019, the assignment will begin to count as credit.

After putting it into Power BI, I have edited the query to have each month as its own field to count if a student has submitted their monthly assingment. I have also added a field for each month to give a value for those students that are active. Students without ActiveGradeDates were active previous to the beginning of the data.

I merged the ActiveGrade query with the Monthly Turn-in along with grouping and pivoting to achieve the above query. 

Monthly Turn-In Table Part 1Monthly Turn-In Table Part 2

I can not seem to figure out how to have all 12 months on the x-axis while having a single line graph to connect the 12 different points from the calculation (# of submissions in month)/(# of active students in same month).

I've tried many different approaches and have yet to figure it out.

P.S. I do need the query how it is for other graphing purposes in my report. Sorry for the long message!

Thanks!

Connor

[v-yiruan-msft]

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

[cmaloyb]

Hi @v-yiruan-msft, 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

[v-yiruan-msft]

Hello @cmaloyb,

You can create a measure as shown 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 b

Best regards

Rena

[cmaloyb]

@v-yiruan-msft ,

Thank you very much, that seems to work. I did find one issue through extending the example. When the year ends (2019 in this case) and the students begin turning in their monthly assignments the following year (2020), those students with Program Start Dates in 2019 (S0003, S0005, S0007) are no longer counted as active students. 

Would you mind explaining what dax in the measure means to help me work this out?

Connor

[v-yiruan-msft]

Hi @cmaloyb ,

Where did the dates you put on line chart Axis come from? Is it from a calendar table? Or from the field Turn-in Date (Month) in Table 1? 

Best Regards

Rena

[cmaloyb]

Hi @v-yiruan-msft, 

The dates on the Axis came from the field Turn-In Date (Month) in Table 1.

R/

Connor

[v-yiruan-msft]

Hi @cmaloyb ,

Please update the formula of 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 b

And the meaning of the formula as follow:

1. Get the current Turn-in Date    

VAR a=MAX('Table 1'[Turn-in Date (Month)])

2. Count the number of the submission

CALCULATE(DISTINCTCOUNT('Table 1'[Student ID]))

3. Count the number of the active student

  1) Get the records which the program start date is blank or less than assignment turn in date 

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

   2) Count of the number of the students who satisfy the above conditions

DISTINCTCOUNT('Table 2'[Student ID])

In addition, please find the details of the functions used in formula in following documentations:

https://docs.microsoft.com/en-us/dax/distinctcount-function-dax

https://docs.microsoft.com/en-us/dax/filter-function-dax

Best Regards

Rena

[cmaloyb]

Hi @v-yiruan-msft,

So I added I changed a Program Start Date in Table 2 to test the measure, however, it still does not include those dates that were passed in the previous year. 

New Table 2:

Student ID	Program Start Date
S0001
S0002
S0003	3/1/2019
S0004
S0005	4/1/2019
S0006	1/2/2020
S0007	10/1/2019

I added more submissions onto Table 1 as well for January 2020 to test the measure.

T0061	B	S0002	1/3/2020
T0062	C	S0003	1/18/2020
T0063	D	S0004	1/19/2020
T00064	G	S0007	1/20/2020

With the measure NumberofsubA put into a Line Graph by Month, January 2020 has NumberofsubA = 1.00

Given the 4 submissions in January 2020 with the 3 blank Program Start Dates & 1  Jan. 2020 Program Start Date (4 Program Start Dates), this would be: 4 Submissions/4 Program Start Dates = 1.00. 

We are still missing those 3 Program Start Dates from the previous year in 2019 (S0003, S0005 & S0007).

I have tried modifying the given measure, however I'm usually left with an error or a calculation that is very far from correct.

Any ideas? 

I would like to thank you so much for your help so far! It has been wonderful learning!

R/

Connor

[v-yiruan-msft · Accepted solution]

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 b

Here 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

[cmaloyb]

@v-yiruan-msft ,

I've run into a requirement change where those who were submitting their monthly assignments will not count in the numerator until their program start date is met.

How do I include this filter in the numerator? I tried using the filtering method used in the denominator, however it does not return the correct results.

R/

Connor

[cmaloyb]

@v-yiruan-msft that seems to work! 

Thank you so much for your help!

R/

Connor

[cmaloyb]

Hi @v-yiruan-msft,

So I added I changed a Program Start Date in Table 2 to test the measure, however, it still does not include those dates that were passed in the previous year. 

New Table 2:

Student ID	Program Start Date
S0001
S0002
S0003	3/1/2019
S0004
S0005	4/1/2019
S0006	1/2/2020
S0007	10/1/2019

I added more submissions onto Table 1 as well for January 2020 to test the measure.

T0061	B	S0002	1/3/2020
T0062	C	S0003	1/18/2020
T0063	D	S0004	1/19/2020
T00064	G	S0007	1/20/2020

With the measure NumberofsubA put into a Line Graph by Month, January 2020 has NumberofsubA = 1.00

Given the 4 submissions in January 2020 with the 3 blank Program Start Dates & 1  Jan. 2020 Program Start Date (4 Program Start Dates), this would be: 4 Submissions/4 Program Start Dates = 1.00. 

We are still missing those 3 Program Start Dates from the previous year in 2019 (S0003, S0005 & S0007).

I have tried modifying the given measure, however I'm usually left with an error or a calculation that is very far from correct.

Any ideas? 

I would like to thank you so much for your help so far! It has been wonderful learning!

R/

Connor

[v-yiruan-msft]

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 b

Best Regards

Rena