cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gibc Frequent Visitor
Frequent Visitor

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

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:

PBI-Tabledata.png

 

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%.

PBI-table.png

Is this doable?

4 REPLIES 4
Super User
Super User

Re: Create table where each row shows grouped data from a different column

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

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
gibc Frequent Visitor
Frequent Visitor

Re: Create table where each row shows grouped data from a different column

Thanks for the feedback Tom.

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

Community Support Team
Community Support Team

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

Hi @gibc

Assume your dataset is like

1.png

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]

2.png

 

 

Best Regards

Maggie

Community Support Team
Community Support Team

Re: Create table where each row shows grouped data from a different column

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