How to show percentages on Stacked bars chart? Filters ignored in measure

[porkaloca]

Hi everyone,

New PBI user so probably I'm asking something very basic, but I don't understand where I'm wrong.

Any help is appreciated.

I need to display some values in a stacked bars chart as percentage of the total for the category (and not of the grand total).

below a screenshot:

The chart on top is my safe base, based on the count.

So, I created a new measure and used it as y-axis value:

PERCENTAGE = COUNTROWS(TBL_SURVEYS)/CALCULATE(COUNTROWS(TBL_SURVEYS),ALLEXCEPT(TBL_SURVEYS,TBL_SURVEYS[REGION]))

The chart seems to be right, but with the filter that  applied in the screen above I expect the percentage to be 100%..

That 20% of the first bar is correct on the total dataset, but not with the filters applied. Why the formula is ignoring the filters on the visual?

What am I missing?

Thanks!

[AlexisOlson · Accepted solution]

It's ignoring the filters because you told it to with ALLEXCEPT. This function removes all filters except for the one(s) you specify to preserve (REGION in your case).

Without knowing what your data and filters look like, I don't know the best way to rewrite your measure but you might be able to fix it by adding in an ALLSELECTED to restore some of the filtering.

PERCENTAGE =
DIVIDE (
    COUNTROWS ( TBL_SURVEYS ),
    CALCULATE (
        COUNTROWS ( TBL_SURVEYS ),
        ALLSELECTED ( TBL_SURVEYS ),
        ALLEXCEPT ( TBL_SURVEYS, TBL_SURVEYS[REGION] )
    )
)