Summarized table with most common values

Question

Hi everyone,&nbsp;I have a table that looks like this:&nbsp;Response IDDateQ1Q2Q3Q4Fjhguy19/12SatisfiedUnsatisfiedImportantNeutralCvbnm46/24NeutralNeutralNeutralNeutral78fghj8/9SatisfiedVery UnsatisfiedNeutralNot Important&nbsp;And I need to create a summarized table that contains the most frequent value in each of the questions by response ID. So, it should look like this:&nbsp;Q1Q2Q3Q4SatisfiedUnsatisfiedNeutralNeutral&nbsp;I've tried many ways to do this and come up empty. I'm also open to doing this in Power Query if there is a way to do that. The actual dataset has 40 of these questions and each question has over 1000 responses.&nbsp;Any suggestions?&nbsp;

tommartens · Answer

Hey&nbsp;Anonymous&nbsp;,
&nbsp;
assuming there is a question where two answers have the same frequency, what is the expected result?What is the table layout? Is each Question a separate column?
Regards,
Tom

anonymous · Answer

TomMartens&nbsp;thanks!&nbsp;Table Layout - each question is a separate column with likert scale responses. There are blanks in the responses. There are two broad question categories - importance and performance as shown below.&nbsp;Some questions have the following answer options:ImportantLess ImportantN/ANeutralNot ImportantVery Important&nbsp;Others have these:N/ANeutralSatisfiedUnsatisfiedVery SatisfiedVery Unsatisfied&nbsp;That's a fair question. We do not have any questions where the reponses were the same for each category but in that case, I'm told the minimum value by alphabetical order works.&nbsp;

tommartens · Answer

Hey&nbsp;Anonymous&nbsp;,
&nbsp;
thanks for the clarification.
Please make sure that the sample data be copied to Power BI easily, currently this is not the case. Consider creating an Excel Sheet, upload the xlsx to OneDrive, Google Drive, or Dropbox and share the link. Make sure that no loigin is required for downloading the spreadsheet.
&nbsp;
Regards,
Tom

reshmasarap · Answer

TomMartens&nbsp;&nbsp;Please try this link -&nbsp;https://docs.google.com/spreadsheets/d/1TKDLQy0p7VUcN9uqeJXdnP_At2mzPBnH/edit?usp=sharing&amp;ouid=111389805641737581541&amp;rtpof=true&amp;sd=true&nbsp;Thank you!

tommartens · Answer

Hey&nbsp;Anonymous&nbsp;,
&nbsp;
I created this measure:
Measure = 
var currentQuestion = SELECTEDVALUE( 'Table'[Questions] )
var t = 
    SUMMARIZE(
        ALLSELECTED( 'Table' )
        , 'Table'[Questions]
        , 'Table'[Answers]
    )
return

SELECTCOLUMNS(
    TOPN(
        1
        , ADDCOLUMNS(
            FILTER(
                t
                , [Questions] = currentQuestion
            )
            , "noOfAnswer"
                , var currentAnswer = [Answers]
                return
                
                CALCULATE(
                    COUNT( 'Table'[Answers] )
                    , 'Table'[Questions] = currentQuestion
                    , 'Table'[Answers] = currentAnswer
                )
        )
        , [noOfAnswer]
        , DESC
        , [Answers]
        , ASC
    )
    , "answer" , [Answers]
)
This measure allows me to create this matrix visual:
Here you will find a pbix file that contains the solution:&nbsp;https://tommartens-my.sharepoint.com/:u:/g/personal/tom_minceddata_com/EYqyDRiNiQpMkiZWZI5V5ocB5Bx0SwifORszjqnDzJIy4Q?e=p04Qz5Note that I turned the question columns into rows using the unpivot function in Power Query.As you can see the from the screenshot, the questions are not sorted. For this reason I recommend creating a Question table and create a relationship. Of course this table must be used for the visualization and inside the measures.
&nbsp;
Hopefully, this provides what you are looking!
&nbsp;
Regards,
Tom
&nbsp;

Forum Discussion

Summarized table with most common values

6 Replies

Related content

Parameter in summarize

SUMMARIZE, VALUES, and other table generation/manipuation with field parameters

Don't Summarize....still summarizes...?

Showing Common values

calculate with summarize vs values

Response ID	Date	Q1	Q2	Q3	Q4
Fjhguy1	9/12	Satisfied	Unsatisfied	Important	Neutral
Cvbnm4	6/24	Neutral	Neutral	Neutral	Neutral
78fghj	8/9	Satisfied	Very Unsatisfied	Neutral	Not Important