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!
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.
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:
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.
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.
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!
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.
We're excited to announce our first cross-community 'Can You Solve These?' challenge!
Visit our Community Blog for articles, guides, and information created by fellow community members.
Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.
Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.