Zoom in on 100% stacked bar chart or use percentage of total but filter values displayed

[Anonymous]

Hi. I have a 100% stacked bar chart that looks like this:

I would like it to just focus on the two green values so it looks like this:

*But* I want to keep the percentages (percentage of the whole group) from the first chart. So for the left bar instead of 13% and 87% it should be 3% and 21%.

Basically something like zooming in on the chart or only showing the parts of the chart above 70%.

Is there a way to acheive this with Power BI?

Thanks

[v-stephen-msft · Accepted solution]

Hi @Anonymous ,

Try to use ALL function.

Here's a simple example.

Percentage = DIVIDE(SUM('Table'[Value]),SUMX(ALL('Table'),[Value]))

Best Regards,

Stephen Tao

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

[parry2k]

@Anonymous so if I understood correctly, you want the remaining portion to be filled with others to make it 100%, in the screenshot, the first bar currently shows 21% + 3 %, and the remaining 76% and in the 2nd bar it will be 67%, is this what you are looking for? 

[parry2k]

@Anonymous what is the error?

[Anonymous]

There were no errors but it just wasn't ignoring the filter. It seemed to have to do with the legend column being a calculated column. It was actually a calculated column that used other calculated columns. It went about 4 levels deep.

I changed all the columns to do their calculations in the transform so now it is a regular column and REMOVEFILTERS now works as expected.

The last part of the puzzle is if I can use the percentage of the unfiltered totals in a 100% stacked chart to even out the visual. Maybe it is better if it isn't but I am curious if there is a way to do it. Right now if I just use a stacked chart I get:

If I change it to a 100% stacked chart it automatically changes the percentages accordingly which is what I would expect but not what I want to see in this case:

If it can't be done I should still be OK. Thanks for sending me down the right path.

[parry2k]

@Anonymous write a measure and use that in the visual:

Fixed % = 
DIVIDE ( 
    [Your measure currently on the value], 
     CALCULATE ( [Your measure currently on the value], 
       REMOVEFILTERS ( Table[Column used on the legend] ) 
    ) 
)

✨ Follow us on LinkedIn and  to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

[Anonymous]

Thanks. I am having problems with REMOVEFILTERS not working though. I think it might have something to do with the field being used in the legend is a calculated column (also derived from another calculated column).

If I try to remove the filter on that Calculated column it does not seem to do anything. If I remove the filter on the native column of the table which I am grouping by (each group gets an individual bar), it removes that filter successfully but that is not what I want.