Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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.
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.
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
99 | |
73 | |
72 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |