A way to summarise data and drill based on column

[MarkS2]

Hi,

I'm having difficulty with manipulating a dataset in PowerBi to get my views/drills and hoped someone could help. I have a set like below;

Index	Type of user	Question 1	Q2	Q3
1	Sales	Approve	Disapprove	Neither
2..	Marketing...	Disapprove	Neither	Neither

The view I'm trying to get is a summary of all questions with an 'overall' % such as Q1 20% approve 70% disapprove, 10% neither. with a drill available on Q1 answer '70% disapprove'  split further by role e.g. is 50% sales 20% marketing 30% I can to do it through manual manipulation but I thought there might be a better way in Bi? Any help would be appreciated!

[FreemanZ]

Hi @MarkS2 

in your real case, how many UserType do you have and how many questions do you have?

It seems more common and analysable to arrange the data like this:

Index	UserType	QuestionID	Answer
1	Sales	Q1	Approve
2	Sales	Q2	Disapprove
3	Sales	Q3	Neither
4	Marketing
	...

this is doable with Power Query

[MarkS2]

Hi @FreemanZ ,

In the real case the data is structured like so, so i think i need two summaries, Q1 summary answer + Q1 summary answer grouped by UserType. If it could then be drilled further interactively to see the individual responses to Q1 via a click throuigh on the summary that would be ideal.

Index	UserType	Question 1	Question 2	Question 3
1	Sales	Agree	Agree	Agree
2	Marketing	Disagree	Disagree	Disagree
3	Engineering	Neither	Neither	Neither
4	Sales	Agree	Agree	Agree
5	Marketing	Neither	Neither	Neither

[v-yangliu-msft]

Hi  @MarkS2 ,

I'm a little confused about your needs, Could you please explain them further? It would be good to provide a screenshot of the results you are expecting.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MarkS2]

Hi Liu,

The below 100% stacked bar, with the ability to click on an answer grouping to to then see a sub grouping of 'people' who answered A1. Added data and call out for clarity

Lets say A1 is made up of 5 answers, i want to be able to click on the bar for A1 and then drill into a details page showing the people who answered A1 with a summary of all their answers.

Sorry if that's not any clearer! Thanks for any help