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