Forum Discussion

cmaloyb's avatar
cmaloyb
Icon for Helper II rankHelper II
6 years ago
Solved

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 monthl...
Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    6 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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's avatar
    cmaloyb
    Icon for Helper II rankHelper II
    6 years ago

    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 IDStudentStudent IDTurn-in Date (Month)
    T0001AS00011/3/2019
    T0002BS00021/3/2019
    T0003CS00031/2/2019
    T0004DS00041/6/2019
    T0005ES00051/23/2019
    T0006FS00061/15/2019
    T0007GS00071/9/2019
    T0008AS00012/16/2019
    T0009DS00042/14/2019
    T0010ES00052/28/2019
    T0011BS00022/1/2019
    T0012GS00072/2/2019
    T0013AS00013/1/2019
    T0014BS00023/1/2019
    T0015CS00033/2/2019
    T0016FS00063/3/2019
    T0017AS00014/1/2019
    T0018BS00024/16/2019
    T0019DS00044/20/2019
    T0020ES00054/20/2019
    T0021FS00064/21/2019
    T0022GS00074/22/2019
    T0023AS00015/1/2019
    T0024BS00025/2/2019
    T0025CS00035/3/2019
    T0026DS00045/4/2019
    T0027ES00055/5/2019
    T0028FS00065/6/2019
    T0029GS00075/7/2019
    T0030BS00026/24/2019
    T0031CS00036/28/2019
    T0032ES00056/29/2019
    T0033GS00076/30/2019
    T0034AS00017/12/2019
    T0035CS00037/22/2019
    T0036GS00077/29/2019
    T0037AS00018/12/2019
    T0038BS00028/13/2019
    T0039CS00038/14/2019
    T0040DS00048/15/2019
    T0041FS0006

    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 IDProgram Start Date
    S0001 
    S0002 
    S00033/1/2019
    S0004 
    S00054/1/2019
    S0006 
    S000710/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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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

      line chart.JPG

      Best regards

      Rena

      • cmaloyb's avatar
        cmaloyb
        Icon for Helper II rankHelper II
        6 years ago

        Anonymous ,

         

        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