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
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?
Solved! Go to 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,
7. Now I can create a 100% stacked bar chart,
or more measures to determine the Yes % and create charts:
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/
Hello,
Unfortunately I do not have that option:
@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
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.
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! 🙂
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/
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/
@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,
7. Now I can create a 100% stacked bar chart,
or more measures to determine the Yes % and create charts:
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/
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
96 | |
91 | |
82 | |
69 |
User | Count |
---|---|
159 | |
125 | |
116 | |
111 | |
95 |