topic COALESCE not working in stacked bar chart, need another solution in DAX Commands and Tips

COALESCE not working in stacked bar chart, need another solution

afaherty — Tue, 21 Dec 2021 19:43:42 GMT

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!

Re: COALESCE not working in stacked bar chart, need another solution

AlexisOlson — Tue, 21 Dec 2021 22:32:04 GMT

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] ) ) )

Re: COALESCE not working in stacked bar chart, need another solution

afaherty — Wed, 22 Dec 2021 12:59:37 GMT

AlexisOlson Thank you! Perfect!