COALESCE not working in stacked bar chart, need another solution

[afaherty]

Hello all!

I have survey data where there are 9 questions, and the answers are on a scale of 0-4:

4 = Consistently

3 = Inconsistently

2 = Minimally

1 = Not at all

0 = N/A

That's not important though.  I was using COALESCE to find the percentage of students (by grade) who answered with each response, with question # on the slicer.

So, slicer:

Percentages in a matrix:

I had used this measure to obtain the percentages:

Response % = COALESCE(DISTINCTCOUNT('Sheet2'[Index]),0)

But I want my stacked bar chart to match the matrix, and it does not.  So therefore I think I need a different DAX measure.

I am not yet able to attach files here, as I don't have access to that feature yet. 

Here is the PowerBI file: 

Stacked Bar Chart HELP! 

Thanks in advance to anyone who can help!

[AlexisOlson · Accepted solution]

It looks like the bar chart doesn't have an axis total quick calculation option, so you'll need to write your own explicit measure.

This should work for both the matrix and the bar chart without applying any 'Show value as' adjustments.

Response % =
DIVIDE (
    DISTINCTCOUNT ( 'Sheet2'[Index] ),
    CALCULATE (
        DISTINCTCOUNT ( Sheet2[Index] ),
        ALLSELECTED ( Sheet2[Survey Likert Response] )
    )
)

[afaherty]

@AlexisOlson Thank you! Perfect!