Average value IF no filter is selected in Table

[dd321]

Hi all,

A continuation of my previous post: https://community.powerbi.com/t5/Desktop/Filter-with-multiple-relationship/td-p/700382

I got great support in that thread and I really appreciate it.

I'm currently using PowerBI to visualize values in my local database using Direct Query. Example of my entities (simplified, not real entities in my database) but illustrates the relationship between them:

• University has one-to-many relationship with Student
• Student has one-to-many relationship with GradeValue
• Grade has one-to-many relationship with GradeValue (it's wrong in the image)

What I currently have:

• Table 1 of Students (Student.name, Student.age)
• Table 2 of Grade with (GradeValue.Value, Grade.Description and Grade.Type)

The goal is whenever I select a specific student in table 1, I want to show the student's corresponding gradevalue, grade description and type. That works perfectly! However, when I don't have anything selected, I want to show the average gradevalue.value in table 2 and also change the field name to "average" instead.

Is it possible to do that?

tl:dr:

Two tables with relationship. If nothing is selected = show average gradevalue.value. If one specific student is selected = show the student's real gradevalue.value (not average).

Sincerely,

[v-chuncz-msft · Accepted solution]

@dd321 ,

You may try AVERAGEX.

• https://community.powerbi.com/t5/Desktop/Percentage-to-goal-by-dynamic-date-range/m-p/642778#M307965
• https://community.powerbi.com/t5/Desktop/monthly-average-of-daily-sums/m-p/291451#M128396