Displaying percentage of total for multiple legends in a visualization

[deadgalaxies]

Hello,

I've searched and searched but still haven't figured out a way to do this based on my data set. I'm putting together a visualization that displays grade distribution for academic courses across multiple college campuses. For each campus, I am showing how many students got A's, B's, C's etc... I am displaying the values as percentages of the total and so far when looking at each campus all is well and looks like this:

The problem is, when I try to add multiple campuses each as it's own legend, the percentages calculate as the total of ALL campuses and not each respective campus. For example, now that 24% of A's from the first screenshot is showing as 1.2%, since it is now calculating across all campuses.

The issue that I'm running into is that my data is individual student records, each with it's own campus and a letter grade (not a number). I have tried using measures but have not been able to figure out how to get this to work. I kind of know what I need to do but I just can't get it. I need to add up all of the grades per campus (a, b, b+, c, etc) and divide by the total number of grades for that campus. Right now I have the axis as letterGrade, the legend as campus, and the value as %GT count of letterGrade if that is helpful.

Any insight would be greatly appreciated! 

[dearwatson]

Hi deadgalaxies,

You can use the magic of CALCULATE and ALLEXCEPTto change the filter context and give you the exact visual you want.

I did a little test pbix and you can see the result shows what I think is corrrect for you.. each percentage ignores the campus filter due to the ALLEXCEPT and so it only shows the break down by grade:

Measures:

Grades = COUNTROWS(Table1)

Total Grades = CALCULATE([Grades],ALLEXCEPT(Table1,Table1[CAMPUS]))

Perc of total = DIVIDE([Grades],[Total Grades],0)

I hope this is what you are after.

Cheers

Greg Nash

[thompsonelyse]

Hi Greg, 

I've used this for problems similar to OP's, but I'm coming across issues when I add in slicers. How is best to deal with this when I have a slicer that cuts down my population, but I still want the items in the legend to sum to 100% across the axis?

Thank you! 

[deadgalaxies]

This is exactly what I'm trying to do!

I'm much closer but still not 100% there. So I think my issue is the Grades = COUNTROWS() measure, because I have some rows with grade types that I don't want to see (i.e. withdrawn, incomplete, pass/fail, etc.), or rows that don't have grades entered. Currently I am filtering my page to only display regular letter grades as pictured:

So I guess what I need is for this filter to also apply to that grades measure, is that correct? How would I go about doing that?  Thank you so much for your help with this!

[dearwatson]

You are precisely correct!

Method 1 - Hardcoded... The cool thing about this is that we only need to change the base measure, everything else will just work:

our new base measure would become a CALCULATE instead of a basic COUNTROWS:

Grade = CALCULATE(COUNTROWS(Table1),Table1[Grade]<>"W",Table1[Grade]<>"P"...etc) OR use the opposite calc ...Table1[Grade]="A"etc...

This hardcodes the filters on the Grade measure so those value never get included... that should work (sorry deleted my example but I'm confident)

Method 2 - use a calculated column

Create a conditional calculated column in the Query editor e.g called "Omit" that looks at the "Grade" column and if the value is W or P or whatever then the output is "Omitted"

This has a simpler measure:

Grade = CALCULATE(COUNTROWS(Table1),Table1[Omit]<>"Omitted")

I would use this method if I thought that Grade column would change in the future... saves coding in the measure and just changing the calucalted column... both methods should work.

Cheers

Greg

[v-qiuyu-msft]

Hi @deadgalaxies,

From your description, it seems that you want to calculate percentage of per grade with each campus, right?

I use some sample data and create a .pbix file for you. I suggest you use 100% Stacked Column Chart, put Campus as Axis values and LetterGrade as Legend. Please see if attached .pbix file meet your requirement.

Best Regards,
Qiuyun Yu

[deadgalaxies]

Hi Qiuyun,

Thank you but this is not exactly what I was looking for. What Greg posted below was what I was trying to do! Thank you for your assistance.