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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register 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
Employee
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,
DataZoe


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,
DataZoe


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,
DataZoe


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,
DataZoe


See my reports and blog at https://www.datazoepowerbi.com/

DataZoe
Employee
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,
DataZoe


See my reports and blog at https://www.datazoepowerbi.com/

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors