Ranking Multiple Dax Measures

Hi Anonymous ,

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.