Summarized table with most common values

[Anonymous]

Hi everyone,

I have a table that looks like this:

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

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:

Q1	Q2	Q3	Q4
Satisfied	Unsatisfied	Neutral	Neutral

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.

Any suggestions? 

[TomMartens]

Hey @Anonymous ,

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: https://tommartens-my.sharepoint.com/:u:/g/personal/tom_minceddata_com/EYqyDRiNiQpMkiZWZI5V5ocB5Bx0SwifORszjqnDzJIy4Q?e=p04Qz5
Note 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.

Hopefully, this provides what you are looking!

Regards,

Tom

[Anonymous]

Hi @TomMartens 

Thank you so much for sharing that. The issue is that there are 40+ such questions and tens of thousands of responses. There is also a larger model that relies on the response IDs being unique. So, unfortunately, I cannot use the unpivot step. 

[TomMartens]

Hey @Anonymous ,

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]

@TomMartens thanks!

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.

Some questions have the following answer options:

Important

Less Important

N/A

Neutral

Not Important

Very Important

Others have these:

N/A

Neutral

Satisfied

Unsatisfied

Very Satisfied

Very Unsatisfied

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. 

[TomMartens]

Hey @Anonymous ,

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.

Regards,

Tom

[reshmasarap]

@TomMartens 

Please try this link - 

https://docs.google.com/spreadsheets/d/1TKDLQy0p7VUcN9uqeJXdnP_At2mzPBnH/edit?usp=sharing&ouid=11138...

Thank you!