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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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