Ranking Multiple Dax Measures

[MacroHardOnFire]

I am creating a PowerBI dashboard based on survey results. The survey was created using SurveyMonkey, and I am working with anonymized individual response data. All of this data is in a single table (call is Survey 2024). The data results make each individual respondant its own row, while the questions themselves are the vast majority of the columns. Most of these questions are numerical data (e.g. rank how you feel about X from 1-5, with 1 as worst and 5 as best). There are a few free response questions, but these are not pertinent to this issue. The survey has three sections: Section 1 has 42 ranked questions and 3 free response questions, Sections 2 has 8 ranked questions, and Section 3 has 25 ranked questions and 0 free response.

I've been displaying the data by creating DAX measures to create the averages for these questions, and placed multiple dax measures in a clustered bar chart to display them against each other. The survey sections have subsections, so this has worked well enough. However, I also want to create a visual of some kind which shows the Top 5 and Bottom 5 ranked questions for Section 1 and 3, regardless of subsection. This has proven to be a very difficult task. I'm dealing solely with summary data, so creating a table visualization creates a new column for each measure rather than a new row, and I don't know how to rank them. I cannot get a matrix to work either. I would like another clustered bar chart, but I cannot figure out how to filter it to only display 5 of the 42/25 measures, even though all of the measures are based on questions which use the exact same 5 point scale. I've looked at other posts about ranking measures, but all of them seem to presuppose some column which is not a DAX measure; in my case, I am trying to rank only the DAX measures.

Any help would be appreciated. I have attached some sample data below for reference on what the spreadsheet is like. This sample dataset would have 10 Dax measures for 10 ranked responses:

Respondent	My job utilizes my skills and abilities.	I am given the flexibility needed to perform my job effectively.	The work I do is meaningful to me.	I am provided the resources needed to effectively do my job.	The compensation and benefits I receive are appropriate based on my job duties, education, training, and experience.	The expectations of my job are clearly communicated to me.	I receive constructive feedback from my supervisor regarding my job performance.	I am recognized for my contributions to my company.	I am recognized for my contributions to my department	My superiors welcome my opinions and ideas	The review process accurately reflects my job performance.	General Comment.
1	4	3	5	4	4	4	3	5	4	3	3	"I am experiencing significant dissatisfaction with my job. There is a lack of communication and support from management, which impacts morale and productivity."
2	5	4	3	5	5	4	5	3	5	4	3
3	3	5	4	3	3	5	4	2	3	5	5	"I am extremely satisfied with my job here. The work environment is supportive, and I feel valued for my contributions."
4	4	3	5	4	4	4	3	5	4	3	2
5	2	4	3	2	2	3	4	1	2	4	4	"I have mixed feelings about my job. While I enjoy the challenging nature of the work, there are instances where I feel micromanaged, which can be frustrating."
6	3	2	4	3	3	2	3	4	3	2	1
7	4	5	3	4	4	5	4	3	4	5	1
8	1	4	3	1	1	4	2	4	1	3	2	"Unfortunately, I feel undervalued and underappreciated in my current position. There is a lack of recognition for hard work and dedication."
9	5	3	2	5	5	3	4	3	5	3	5
10	2	4	5	2	2	4	3	2	2	4	3
11	4	3	1	4	4	4	5	3	4	2	2
12	5	4	3	5	5	4	5	3	5	4	2
13	3	5	4	3	3	5	4	2	3	5	5	"While I appreciate the opportunities for advancement, I sometimes feel overwhelmed by the workload. Finding a balance between work and personal life has been challenging."
14	4	3	5	4	4	4	3	5	4	3	3
15	2	4	3	2	2	3	4	1	2	4	2
16	3	2	4	3	3	2	3	4	3	2	4
17	4	5	3	4	4	5	4	3	4	5	4	"I find great fulfillment in my role at the company. The opportunities for growth and development are abundant, and I am grateful for the positive work culture."
18	1	4	3	1	1	4	2	4	1	3	2
19	5	3	2	5	5	3	4	3	5	3	1

[v-yilong-msft · Accepted solution]

Hi @MacroHardOnFire ,

Based on your problems, here are my thoughts.

You can create a table called "Survey 2024" with columns for "Question", "Response", and "Partial" columns and then write a Measure with a Top5Rank.

Top5Rank = 
VAR SummaryTable =
    SUMMARIZE (
        'Survey 2024',
        'Survey 2024'[Question],
        "AvgScore", AVERAGE ( 'Survey 2024'[Response] )
    )
VAR SortedTable =
    ADDCOLUMNS (
        SummaryTable,
        "Rank",
        RANKX ( ALLSELECTED ( 'Survey 2024'[Question] ), [AvgScore], , DESC )
    )
RETURN
    IF (
        [Rank] <= 5,
        [AvgScore],
        BLANK ()
    )

This measure calculates the average score for each question, ranks them in descending order, and returns the average score for the top 5 questions. You can also adjust the bottom 5 metric if needed.

Now that you have the ranking measure, create a clustered bar chart. Add the “Question” column to the Axis (categories) and the “Top5Rank” measure to the Values. Filter the visual to show only the Top 5 questions.

You can add a slicer or filter to allow users to select the subsection, and then modify the measure to calculate the average score within that subsection.

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MacroHardOnFire]

I was able to use this as a basis to solve the problem. Thank you!