Filter Stacked Bar Charts based on Total Bar / Axis Item Value, NOT by Legend Item Values

[SAB3_User]

Hello, all! I have put together a stacked visual that shows a request count across different Subjects with a legend that shows the requests as one of three different categories (for the sake of simplicity, let's say Colors: "Green," "Yellow," or "Red"). For the purpose of this specific request, the visual is a stacked bar chart (percentage) where the axis is "Subjects" and the legend is the Color category.

I am trying to create a filter on this visual so that it ONLY shows Subjects that have an aggregate count of 50 requests or more. Specifically, as we only want to look at Subjects that have received a higher volume of Requests.

Applying this filter with Subjects as the axis without the legend is a simple task. However, the moment I apply a legend (Colors) and make this a stacked bar chart the filters are now being applied to the created measures for the individual Color portions for each Subject rather than the aggregate number.

That is, if Subject A has 52 Green, 54 Yellow, and 32 Red items the visual will now only pick up the Green and Yellow portions for that Subject. Similarly, if Subject B has 49 of each Color, it will now be completely excluded despite the aggregate count being above 50 because each legend component is below 50. Is there any way to apply a visual-level filter that keys to the Subject / Axis Item Aggregate count while maintaining the legend for a % stacked bar chart, instead of filtering the legend components for that Subject?

Or, would the only feasible solution here be to create a separate table with a list of all Subjects and a column with a conditional label (“Include” or “Exclude”) based on the value/measure of Requests for that Subject? This is likely not the ideal solution, as the list of Subjects may change as new ones are added. Furthermore, we would also want to create the same visual with similar total Request count thresholds and legends for other groupings / Axes (e.g., Region of Request Origin; Calendar Year Quarter) that may also dynamically change to include newer items over time. Unless we can make the tables on which these conditional labels would be applied dynamically populate with the list of all Subjects (or other Column values) that appear in the connected data set when it is refreshed?

Any help would be much appreciated!

[SAB3_User · Accepted solution]

Was able to figure this out after utilizing CALCULATE and ALLEXCEPT in a new measurement.

[Zana2023]

Please what was the solution?

[leesh13]

I'm having the same problem but can't find a solution using calculate with allexcept. Can you share your formula? Thanks!

[Greg_Deckler]

It's hard to picture/visualize this and it's a lot of words. 

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[SAB3_User]

@Greg_Deckler Sorry about that. Hopefully this might be a bit simpler.

• I have created a visual that shows the number of Requests (each request is an individual entry) with the X axis representing different "Subject" names/identifiers. There is also a slicer that can be used to narrow to a Region tag, but that is not important for the time being.
• The visual legend is set to the Color Code for the request (there are three possible values)
• I would like to be able to filter the visual so that the graph only includes Subjects with 50 or more overall Requests (I have a measure for this); meaning the aggregate count of green, yellow, and red coded items for that Subject is above 50
• However, my issue is that when I have the legend enabled, the Request Count > 50 filter applies to the individual Request Count at the "Color" level for each "Subject," not the Subject's Aggregate Request Count. I would like to be able to employ a filter based on the aggregate Request Count by Subject
• The filter works on a matrix, but not the stacked bar visual or stacked bar % visual (which is the requested output)

I was able to somewhat achieve the desired effect by creating a separate table with a column that counted the requests based on the Subject name, and then adding another column with a conditional "Include" value based on the returned column value (and filtering the visual to only show items for Subjects marked as "Include"). However, this was not 'dynamic' in the sense that its Request count column is not impacted when a slicer is used to filter to a specific region or group of regions.

Sample of Individual Data Point

Subject	Color Code	Region	Date Submitted
Subject A	Green	Northeast	12/15/2019