Forum Discussion
Line Graph with Multiple Months and Averages
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
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
13 Replies
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
Hi 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
