cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

Displaying percentage of total for multiple legends in a visualization

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:

 

Screen Shot 2017-04-06 at 6.58.11 PM.png

 

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.

 

Screen Shot 2017-04-06 at 6.59.34 PM.png

 

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! 

 

 

 

 

5 REPLIES 5
Highlighted
Community Support
Community Support

Re: Displaying percentage of total for multiple legends in a visualization

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

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Responsive Resident
Responsive Resident

Re: Displaying percentage of total for multiple legends in a visualization

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)

 

 

 

Capture.PNG

 

I hope this is what you are after.

 

Cheers

Greg Nash

"The commonality between science and art is in trying to see profoundly - to develop strategies of seeing and showing." - Edward Tufte
Highlighted
Regular Visitor

Re: Displaying percentage of total for multiple legends in a visualization

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.

Highlighted
Regular Visitor

Re: Displaying percentage of total for multiple legends in a visualization

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:

 

Screen Shot 2017-04-10 at 4.23.56 PM.png

 

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!

 

Screen Shot 2017-04-10 at 4.30.34 PM.png

 

 

 

 

Highlighted
Responsive Resident
Responsive Resident

Re: Displaying percentage of total for multiple legends in a visualization

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

 

"The commonality between science and art is in trying to see profoundly - to develop strategies of seeing and showing." - Edward Tufte

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors
Top Kudoed Authors