Help crossfiltering table visual on different columns

[jp8888]

Hi all,

I'm having trouble with something that seems like it should be very simple. 

I have a database of classes, instructors, and scores from evaluation surveys (among other things). I need cards to show averages from a few different things based on the selected row in a table visual.

We'll call the relevant columns main[class name], main[instructor name], main[instructor avg] (calculated column), main[overall avg] (calculated column that includes instructor avg as well as a few other metrics).

Here's a mockup of the table viz:

Class	Instructor
Game Design 4/14/20	Mario Brothers
Technical Drawing 9/9/20	Bill DaBridge
Beatboxing 10/15/20	Ricky Bootsncats
Plumbing 8/12/20	Mario Brothers

This is the easy part.

I need cards to show the instructor avg and overall avg for the selected class in the table viz. Like so:

Selected Class: Game Design taught by Mario Brothers

Class Instructor Avg 8.5

Class Overall Avg 8.1

_______________________________________________________________________

The hard part is that I also need cards to show the OVERALL instructor avg and overall avg across all the classes that instructor has taught. Like so:

Selected Trainer: Mario Brothers

Overall Instructor Avg 9.7

Overall Overall Avg 9.5

In other words, I need to crossfilter two of my cards on the selected table row, and I need to crossfilter two cards based only on the instructor value from the selected row.

I've been able to use things like Measure1 = SELECTEDVALUE(main[instructor name], "NA") to isolate the instructor value from the table viz, but I can't figure out where to go from there.

I've tried things like

Measure2 = VAR inst = Measure1

RETURN

           CALCULATE (

                        AVERAGE ( main[instructor avg] ),

                         main[instructor name] = inst )

But it's not working for me (I know this syntax may not be 100% accurate--this is just off the top of my head). I'm still ending up with my Measure2 on the card showing either the averages only for the selected table row OR the entire avg across all instructors (depending on whether I limit visual interactions).

By the way the reason I'm using a table viz and not a slicer is so that the user will be able to easily see which classes were taught by which instructors.

Is this way harder than it should be, or am I just missing a silly little thing?

Unfortunately, I can't post the file, and it would take a lot of work to sanitize the data and create a mockup, so hopefully this makes sense. Please let me know if I need to clarify anything.

Any help would be deeply appreciated. Thanks!

[v-alq-msft · Accepted solution]

Hi, @jp8888 

I'm not very clear about the expected result. You may try the following measure to see if it works.

Result =
VAR inst = [Measure]
RETURN
    CALCULATE (
        AVERAGE ( main[instructor avg] ),
        FILTER ( ALL ( 'main' ), main[instructor name] = inst )
    )

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-alq-msft · Accepted solution]

Hi, @jp8888 

I'm not very clear about the expected result. You may try the following measure to see if it works.

Result =
VAR inst = [Measure]
RETURN
    CALCULATE (
        AVERAGE ( main[instructor avg] ),
        FILTER ( ALL ( 'main' ), main[instructor name] = inst )
    )

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jp8888 · Accepted solution]

Allan,

Your suggestion does exactly what I need it to do. Thank you!

I actually figured out a solution of my own very very shortly before seeing your post. I ended up creating an additional table containing only [instructor name] (grouped) and [instructor avg] and [overall avg] (both summarized on average then joined to 'main' on [instructor name].

However, I think your solution is a bit more elegant. I learned something new from this.