Data Table Percentage Question

[ianallen13]

Hello, 

I am hoping that someone on here can guide me in the right direction with this issue.

I have a data with multiple columns (QA Items) and with each column, there are only three responses: Yes, No, N/A.  

I can write DAX expression measure to get the accuracy percentages for each, but where I remain stuck is how to present this in a visual, preferably in a stacked bar graph that shows the accuracy percentages (see example below for other table): 

Here is a sample of part of my data table in SharePoint that I am using as a source: 

Yes = It was there

No = it was not there

N/A = it did not apply

I'm at a loss as to where to go next.  I was thinking if I need to create a calculated table, but I'm not very skilled on how to do that - so seeing if anyone out there has any ideas?  

[ianallen13]

Hello, 

Unfortunately I do not have that option: 

[DataZoe]

@ianallen13 yes, for that one you are limited to just picking one for each of the options. 🙂 It's the other one that has the fancy options.

[ianallen13]

Sorry to sound dumb, but what is the other one?  Each visual that I have chosen does not have that option for this.  So far, I can do only select that when I create a table or matrix in PBI

[ianallen13]

Thank you!  Yes, I just filtered out the N/A's and it worked perfectly.  Thank you again so much for your help.

Now the only other thing I cannot figure out - I saw that you were able to do some conditional formatting to the bar graph, but when I try to do that - I am not presented with the option.  Any thoughts?

[DataZoe]

@ianallen13 For the bar chart, to get the gradient option you go to "Data colors" and pick the little "Fx" button. This allows you to pass it a measure and choose how to color it.

and in the preview of the new visualization pane, it's here:

I chose gradient, but you can also impliment rules too, so you can highlight when it drops below or above a certain percent also. You can also base it on a different measure than the one used in the bar.  Lots of options! 🙂

[ianallen13]

Thank you for this and for the example file, but I do have an issue... 

The calculations showing are indicating the percentage of YES's for each attribute, but I don't want the N/A's to count in the overall calculations.  

e.g. 

If I have "School Info Updated" with 166 responses: 

N/A: 141

NO: 6

YES: 19 

I don't want it to be 19/166, but I want it to be 19/25 = 76%.  Do I have to do a new measure for each attribute then?

[ianallen13]

Disregard this - I answered my own question.  

[DataZoe]

@ianallen13 what was your solution so others can also find it helpful? 

The way I usually do this is either change the measure or simply remove the N/A's entirely, either in Power Query or in the all pages filter in the filter pane. To change the measure I use this approach:

Yes Responses Percent = DIVIDE(CALCULATE([Responses],'Table'[Value]="YES"),CALCULATE([Responses],'Table'[Value] in {"YES","NO"}))

[DataZoe · Accepted solution]

@ianallen13 I would probably unpivot the data and then create the %'s in a measure. 

1. In "Transform data"/Power Query, if there is no ID column, I'd create one using "Add column" > "Index Column"

2. Then I would select all the columns except for the Index

3. Then go to "Transform" > "Unpivot Columns"

4. Now I have "Attribute" which is my headers before, and "Value" which is the data inside.

5. Close and Apply to get back to the visual layer

6. I created a measure to count the number of responses using the Index column, 

Responses = DISTINCTCOUNT('Table'[Index])

7. Now I can create a 100% stacked bar chart,

or more measures to determine the Yes % and create charts:

Yes Responses Percent = DIVIDE(CALCULATE([Responses],'Table'[Value]="YES"),[Responses])

I've attached the PBIX so you can take a look.