Create table where each row shows grouped data from a different column in the dataset

[gibc]

I need to create some tables that show survey response data to different questions all in the same table.

The results for each question are in their own column in the dataset. Each row represents shows data from a different column in the dataset.

For this example, each question has 7 answer options (numbers 0-6). 

The result that i want to show is the count of the grouped answers (5+6) /count of total number of answers.

In excel my formula for this is: =COUNTIF(Survey[question],">=5")/COUNT(Survey[question])

This is what i have in excel and want to recreate:

I can create a table in Power BI for a single question (column of data) - and i know how to group the answer options and have these show as a percent of the total number.

I don't know how to only show one of these 'groups' and have the % stay the same.

If i remove the other groups and only show one, then the % changes to 100%.

Is this doable?

[v-juanli-msft]

Hi @gibc

Assume your dataset is like

Create measures, then add columns and measures to the table visual

count of the grouped answers (5+6) =
CALCULATE (
    COUNT ( Sheet1[answer] ),
    FILTER ( ALLEXCEPT ( Sheet1, Sheet1[question] ), [answer] >= 5 )
)

count of total number = CALCULATE(COUNT(Sheet1[answer]),ALLEXCEPT(Sheet1,Sheet1[question]))

percent = [count of the grouped answers (5+6)]/[count of total number]

Best Regards

Maggie

[TomMartens]

Hey,

unfortunately I do not understand the layout of your table(s), for this reason it's difficult to provide you any meaningful hints.

Maybe this link gets you started:

https://www.daxpatterns.com/survey/

If you need mor help, upload you pbix file to onedrive or dropbox and share the link.

Regards,

Tom

[gibc]

Thanks for the feedback Tom.

I'll have a go at editing the original question to see if i can do any better.

[v-juanli-msft]

Hi @gibc

Does TomMartens' suggestion really help you, if so, could you kindly accept his answer as a solution?

If not, please feel free to ask any question.

It would be better for analysis of you to provide an example table.

Best regards

Maggie