Forum Discussion

ianallen13's avatar
ianallen13
Helper I
4 years ago
Solved

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:...
Need Help
Tips and Tricks
  • DataZoe's avatar
    DataZoe
    4 years ago

    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.

DataZoe's avatar
DataZoe
Microsoft Employee
4 years ago

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.