I have a Power BI table, with each row showing a teacher and the number of their students who have passed a test. There are 3 rows to the table.
Here's an example:
Teacher | # students who passed | # students who took test | % passing |
---|---|---|---|
Teacher A | 16 | 35 | 45.7% |
Teacher B | 11 | 30 | 36.7% |
Teacher C | 19 | 32 | 59.4% |
I also have a gauge that shows the total amount of students who have passed the test (the value of the gauge) out of the total number of students (the maximum value of the gauge).
The gauge callout value is set to be the % of passing students.
I have the gauge subtitle set to dynamically show the teacher so that when I click a specific row of the table, the gauge updates to show only that teacher's student data and the subtitle will show that teacher name and the callout value will show the passing % for just that teacher.
This is all working correctly.
The problem is that when there is no selection made in the table (and the gauge goes back to showing the totals for all teachers), the subtitle will display the first teacher name, when it should either not show anything or preferably, it should show a "default" subtitle, like "All teachers combined".
Similarly, the gauge callout value changes when a teacher is selected from the table, but when no teacher is selected the callout value shows the sum of all the data in the % complete column (which is a number greater than 100%), when I would like for the callout value to not be displayed at all in that case or have that % divided by the number of teachers to at least give an average %.
Don't use the [% passing]
column - instead create a Measure for this:
Pass % =
DIVIDE(
SUM('YourTable'[# students who passed]),
SUM('YourTable'[# students who took test])
)
Set the Format to Percentage
for this measure, with the desired number of decimals.
By using this measure, it will always calculate the right percentage whether one or multiple teachers are selected (eg total).
As for your subtitle, create a Measure similar to:
Gauge Subtitle =
var teacherCount = COUNTROWS(DISTINCT('YourTable'[Teacher]))
RETURN
SWITCH(TRUE(),
NOT ISFILTERED('YourTable'[Teacher]), "All teachers",
teacherCount > 3, teacherCount & " teachers selected",
CONCATENATEX(DISTINCT('YourTable'[Teacher]), 'YourTable'[Teacher], ", ")
)