Forum Discussion

cw900's avatar
cw900
Icon for Helper I rankHelper I
2 years ago
Solved

Line and Stacked Colum - How to Show Average on Line?

I want to show a student's login activity per day (Event_Type = "Login") as columns and I want to show the course average for the course that the student is on as the line on the same graph.

 

Here is the data: https://www.dropbox.com/scl/fi/70aa2cgr1chegmrdlitdg/exampledata.xlsx?rlkey=2m5ox5ix7v65v5dsf3usl4e8q&st=zj3leo55&dl=0

 

 

What measure do I need for the line?

 

My measure for the columns is as follows: 

Count of Logins =
CALCULATE (
    SUM ( CourseData[Total] ),
    CourseData[Event_Type] = "Login"
)

 

My data is structured as follows and note that this table contains several students and several courses:

 

How do I create a measure that says take the course of the student selected and calculate the average logins for that course and plot on the same visual?

 

The expected result is that when you selected Nina who is studing Geology, the line and column chart will show Nina's logins every month as columns and plot the average logins for Geology on the line.

 

 

 

Need Help
  • _AAndrade's avatar
    _AAndrade
    2 years ago

    In the Allexcepted put the column date that your are using in the charte.

    Allexcepted will remove all filter on that table except on the column you mention, so I think it Could solve your problem.

    Try...

15 Replies

  • Uzi2019's avatar
    Uzi2019
    Icon for Community Champion rankCommunity Champion
    2 years ago

    Hi cw900 

    A simple avg formula is total sum / no of count.
    Create same formula through measure.

    Add your formula here in this property of bar chart

     

     

    I hope I answered your question!

     

    • cw900's avatar
      cw900
      Icon for Helper I rankHelper I
      2 years ago

      How do I identify the course that the student is on? And how to I tell my measure for the average to ignore the student that I've selected and get me the average for the course they are on?

  • _AAndrade's avatar
    _AAndrade
    Icon for Resident Rockstar rankResident Rockstar
    2 years ago

    Hi cw900,

    Please try this Dax measure:

    AverageCourse = 
AVERAGEX(
        ALL(T_Logins[Student]),
        CALCULATE(
            AVERAGE(T_Logins[Total])
        )
)


    T_Logins is my table, so you need to change this to your table name. I did some test from my side and it's working fine.

    • cw900's avatar
      cw900
      Icon for Helper I rankHelper I
      2 years ago

      Hi

       

      I don't think I was clear in my question. There are different courses in this table. Using that measure the average line is the same for every student because it is not taking into account the course that they are on.

      • _AAndrade's avatar
        _AAndrade
        Icon for Resident Rockstar rankResident Rockstar
        2 years ago

        Please provide one example with some data and the expected output.

        I think that way would be easy to underdtand