Help with Percent on Stacked Bar Chart

[smedina2020]

Hi

I made a stacked bar chart to show gender data over a period of years, I was able to show the percent with this measure:

Gender % = DIVIDE(DISTINCTCOUNT('Table1'[PIDM]),CALCULATE(DISTINCTCOUNT('Table1'[PIDM]),ALLEXCEPT('Table1','Table1'[Year])))

Now the problem I have is that when I add a slicer (lets use state as example) when I click on one of the options the percent show is not within what is filtered.

Example: No slicer shows for 2014 61% Male and 39% Female click on Oregon state slicer and it shows 22% Male and 33% Female which clearly does not add 100%

What I'm doing wrong?

[v-chuncz-msft · Accepted solution]

@smedina2020 

You may refer to the post below.

https://community.powerbi.com/t5/Desktop/Percentages-to-multi-line-report/m-p/378504#M172040

[jthomson]

Probably something to do with the allexcept function you're using, I'm guessing that your numerator will count just Oregon in 2014, but the denominator will count every state in 2014, i.e. it'll show in your data 22% of 2014 is male in Oregon, because you're telling it to ignore every filter context except the year. Seems an unnecessarily complex formula to be honest

[smedina2020]

So... any sugestions?

[jthomson]

I'd rewrite the formula, but without seeing your data model or data it's hard to do so, so just add whatever column you're feeding into the state filter into the allexcept bit of the existing measure