Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
ianallen13
Helper I
Helper I

Data Table Percentage Question

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

ianallen13_2-1635540603573.png

 

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

ianallen13_1-1635540453160.png

 

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?  

 

1 ACCEPTED SOLUTION
DataZoe
Microsoft Employee
Microsoft Employee

@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"

DataZoe_1-1635558152588.png

 

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.

DataZoe_2-1635558191593.png

 

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,

 
DataZoe_3-1635558246314.png

 

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

 

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

DataZoe_0-1635557959126.png

 

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

Respectfully,
Zoe Douglas (DataZoe)



Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/

View solution in original post

9 REPLIES 9
ianallen13
Helper I
Helper I

Hello, 

 

Unfortunately I do not have that option: 

 

ianallen13_0-1637076706572.png

 

@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.

Respectfully,
Zoe Douglas (DataZoe)



Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/

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
Helper I
Helper I

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?

@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.

 

DataZoe_0-1637076168605.png

 

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

DataZoe_1-1637076307612.png

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! 🙂

Respectfully,
Zoe Douglas (DataZoe)



Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/

ianallen13
Helper I
Helper I

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?

 

Disregard this - I answered my own question.  

@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"}))

Respectfully,
Zoe Douglas (DataZoe)



Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/

DataZoe
Microsoft Employee
Microsoft Employee

@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"

DataZoe_1-1635558152588.png

 

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.

DataZoe_2-1635558191593.png

 

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,

 
DataZoe_3-1635558246314.png

 

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

 

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

DataZoe_0-1635557959126.png

 

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

Respectfully,
Zoe Douglas (DataZoe)



Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

Find out what's new and trending in the Fabric community.