cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

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:

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!

6 REPLIES 6
Responsive Resident

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:

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
Regular Visitor

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!

Regular Visitor

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!

Responsive Resident

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:

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:

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
Community Support

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.
Regular Visitor

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.

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors