Small multiples stacked bar chart -- how to omit missing data from Y axis

[barbara_oi]

I have a stacked bar chart with City as the Y-axis, Funding amount as the X-axis, Source as the legend, and year as a small multiple. 

Some cities do not have rows of data for all of the years displayed. This means that they show up in the y-axis but are blank. I would like for cities with 0 funding in a given year to show up as blank, but I don't want this same behavior for cities that simply don't have a row for the given year. 

I tried using a visual-level filter as suggested on this post (https://community.fabric.microsoft.com/t5/Desktop/Small-multiples-chart-filtered-for-each-group/m-p/...) but it doesn't work, I think because year isn't blank per se, it just doesn't exist for some city/year combinations. Is there any way to omit cities without a row for a given year from the y-axis for that year? Thanks in advance

[Anonymous]

Hi @barbara_oi ,

I create a table as you mentioned.

Then I create a measure and here is the DAX code.

Measure = 
IF(
    SUM('Table'[FundingAmount]) = 0,
    BLANK(),
    SUM('Table'[FundingAmount])
)

Finally you will see what you want.

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[barbara_oi]

Hi Yilong Zhou,

Thanks for taking the time to respond. Unfortunately this does not address the issue I am running into. The year/city combination in my data is already blank, not 0. As you can see in the image you shared, there is still a space for City B in all four quadrants, which takes up valuable space (I also have this formatted differently, with city on the y axis and a single view instead of multiple quadrants). 

I'm including an image as a better example of my problem. As you can see, in 2023 all nine cities are showing up on the y axis. However, the data for cities 3, 6, 7, and 8 are all blank. I want to squish together the other cities so the visual does not take up so much space, but when you scroll down to earlier years, I want all cities that have data to show up (including cities 3, 6, 7, and 8 when they have funding). Is this possible?