Aggregating survey/audit responses (by column and row)

[KLee16]

Hello, 

I am fairly new to DAX and PowerBI.

I have 2 queries I would love assisitance on.

I have a MS Form that is linked to as my data source and the survey/audit responses are categorised into Topics and then there are a set of related questions in which each question can only have 1 of 3 value responses (No Issue, Needs Improvement, Stop and Correct). 

I need to be able to aggregate the total number of responses per topic (similar to how i have shown below) to be shown in a pie/gauge chart.

My second query is, is it possible to then  drill down to see what questions relate to the response as my ultimate goal is that I can see majority of audits are showing the requirements in Topic 1 - Q2 needs attention as most responses are "Stop and Correct".

Appreciate your assistance.

Kind Regards, K

My Example Data Step

My Example Data from MS Forms Excel Responses Sheet

Visual per topic I would like to achieve

Chart I would like to achieve per topic

[amitchandak · Accepted solution]

@KLee16 , I one the thing, which I can thing is unpivot all "Topic - "columns into rows, see if that can help

https://radacad.com/pivot-and-unpivot-with-power-bi

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[KLee16]

@amitchandak Thank you. 

This has worked perfectly

I appreciate your prompt repsonse.