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

[cw900]

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=2m5ox5ix7v65v5dsf3usl4e8...

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.

[_AAndrade]

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]

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]

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

I think that way would be easy to underdtand 

[cw900]

Here's the data:

https://www.dropbox.com/scl/fi/70aa2cgr1chegmrdlitdg/exampledata.xlsx?rlkey=2m5ox5ix7v65v5dsf3usl4e8...

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.

[_AAndrade]

Please take a look it this is what you're looking for:

My AverageCourse measure it this:

AverageCourse = 
IF(
    NOT(ISBLANK([Count of Logins])),
    CALCULATE(
        [SimpleAvg],
        FILTER(
            ALL(T_Logins),
            T_Logins[Event_Type]="Login" && T_Logins[Student_Course_Name] IN VALUES(T_Logins[Student_Course_Name])
        )

    )
)

[cw900]

What is 

[SimpleAvg]

?

Also, that doesn't look right because the average value doesn't change each day. I need average value for the course on each day, so it will change every day. 

[_AAndrade]

@cw900,

See if this solve your problem:

Measures:

SimpleAvg = 
AVERAGE (T_Logins[Total])

New Avg = 
IF(
    NOT(ISBLANK([Count of Logins])),
    CALCULATE(
            [SimpleAvg],
            REMOVEFILTERS(T_Logins[Student]),
            T_Logins[Event_Type]="Login"
    )
)

[cw900]

Ok that is much closer. I changed the measure to this:

New Avg v2 =

    CALCULATE(

            AVERAGE (CourseData[Total]),

            CourseData[Event_Type]="Login",

            REMOVEFILTERS(CourseData[Student])

    )

This works in the example here but when I apply this to my real data it doesn't work. I think because my real table contains many other fields other than Student_Course_Name.

Is there any way in that measure to say only calculate the average based on Student_Course_Name?

[_AAndrade]

Try something using Allexcepted.

DAX is all about context and for that reason is important to have the right example.

In my example is working fine on my side...

[cw900]

Hmmm. ALLEXCEPT looks useful but I'm struggling to work out the implementation.

The following measure just gives me the same average on each date.

New Avg v3 =

    CALCULATE(

            AVERAGE (CourseData[Total]),

            CourseData[Event_Type]="Login",

            ALLEXCEPT(CourseData, CourseData[Student_Course_Name])

    )

[_AAndrade · Accepted solution]

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

[cw900]

That was it 🙂

Thank you.

[_AAndrade]

Great. You're welcome.

[Uzi2019]

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]

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?