Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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:

 

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?

Need Help

4 Replies

  • TomMartens's avatar
    TomMartens
    Icon for Super User rankSuper User
    8 years ago

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      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's avatar
        v-juanli-msft
        Icon for Community Support rankCommunity Support
        7 years ago

        Hi Anonymous

        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

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    8 years ago

    Hi Anonymous

    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