Forum Discussion
Data Table Percentage Question
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.
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! 🙂