Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
MacroHardOnFire
Frequent Visitor

Ranking Multiple Dax Measures

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:

RespondentMy 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 departmentMy superiors welcome my opinions and ideasThe review process accurately reflects my job performance.General Comment.
143544435433"I am experiencing significant dissatisfaction with my job. There is a lack of communication and support from management, which impacts morale and productivity."
254355453543 
335433542355"I am extremely satisfied with my job here. The work environment is supportive, and I feel valued for my contributions."
443544435432 
524322341244"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."
632433234321 
745344543451 
814311424132"Unfortunately, I feel undervalued and underappreciated in my current position. There is a lack of recognition for hard work and dedication."
953255343535 
1024522432243 
1143144453422 
1254355453542 
1335433542355"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."
1443544435433 
1524322341242 
1632433234324 
1745344543454"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."
1814311424132 
1953255343531 
1 ACCEPTED SOLUTION
v-yilong-msft
Community Support
Community Support

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.

vyilongmsft_0-1707366742839.png

vyilongmsft_1-1707366811828.png

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.

View solution in original post

2 REPLIES 2
v-yilong-msft
Community Support
Community Support

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.

vyilongmsft_0-1707366742839.png

vyilongmsft_1-1707366811828.png

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.

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors