Power bi sort by measure - EnjoySharePoint

In this Power bi tutorial, we will learn power bi sort by measure with different types of examples. And also we will discuss the below points:

Sort by measure power bi
Sort column by measure power bi
Sort matrix by measure power bi
Power bi sort by measure not in visual
Power bi sort by measure not in table
Power bi sort by measure by another column
Power bi slicer sort by measure
Power bi sort legend by measure

Table of Contents

Sort by measure in Power bi

In Power bi, we can choose a column, and then we can sort the column in ascending order or descending order by using RANKX() in measure.

Here we will see how to sort the column in ascending and descending order by using measures.

We have two tables, one table is a data table that contains the name, average, and dismissals column.

And another table is the slicer table which contains only one column, this table is used in the slicer to ascending and descending the order of the average and dismissals column.

Data table

Slicer table

Load the data using get data.
Click on the new measure from the ribbon
Then write the below measure to get the MAX of average and dismissals.

Measure = SWITCH(SELECTEDVALUE('Slicer table'[Slicer]),"Average",MAX('Data'[Average]),"Dismissals",MAX('Data'[Dismissals]),BLANK())

Now we will sort the Average column and Dismissals column ascending and descending order respectively by using the measure.

Click on the new measure from the ribbon.
Then write the below measure:

Measure 2 = SWITCH(SELECTEDVALUE('Slicer table'[Slicer]),"Average" ,
RANKX(ALLSELECTED('Data'),'Data'[Measure],,ASC,Dense),
"Dismissals",
RANKX(ALLSELECTED('Data'),'Data'[Measure],,DESC,Dense))

Now to check the measure, select slicer from the visualization pane.
Then in the field, drag and drop the slicer column from the field pane.

Now select stacked column chart from the visualization pane.
In the axis field, drag and drop the name column from the field pane.
In the value field, drag and drop the Measure from the field pane.
Then in the slicer select Average to show the value in Ascending order.

Read Power BI waterfall chart

Sort column by measure power bi

Here we will see how to sort column by measure in Ascending order in power bi desktop.

We will use below sample table, to sort the sales column in ascending order in power bi by using RANKX().

Load the data using get data.
Click on the new measure from the ribbon.
Then write the below measure:

sort asc = RANKX(ALL('sales'),CALCULATE(MAX('sales'[Sales])),,ASC,Dense)

No to check the measure, click on the table visual from the visualization pane.
In the value field drag and drop the Product column, name column, and sort asc measure.

Click on the more icon(…) in the canvas or top right of the table visual.
Then select sort by -> Sort asc, and then select sort ascending.

Now in the below screenshot you can see column is sorted in ascending order by measure in power bi.

Read Power Bi Schedule Refresh

Sort matrix by measure power bi

Here we will see how to sort the date column in matrix by using measure in power bi desktop.

We will use the below sample data, to show the newest date in the power bi matrix.

Load the data using get data.
Click on the new measure from the ribbon.
Then write the below measure:

sort desc = RANKX(ALL('sales'),CALCULATE(MAX('sales'[Date])),,Desc,Dense)

Now to check the measure, select the matrix visual from the visualization pane.
In the column field, drag and drop the date column from the field pane.
In the value field, drag and drop the sales column, and sort desc measure from the field pane.

Now we will see hide the sort desc matrix from the visualization.

Now click on the more icon(…) in the canvas, click on the sort by -> sort desc.
Then click on the sort descending.

For this, click on the Formatting icon in the visualization pane.
Expand the field formatting, select the measure, then change the font color and background color to white.
Turn on apply to header, apply to values, apply to total, and apply to subtotal.

Now you can see the date is sorted by descending order in power bi matrix.

Read Power bi area chart

Power bi sort by measure not in visual

Here we will see how to sort by a measure that is not included in a visual.

We will use the order sample table, you can download it from here.

Here we will use the number of customer measures to sort the visual in ascending and descending order without showing it in the stacked column chart visual.

We are using the below customer measure to sort the visual by adding in the tooltip field of the stacked column chart visual.

Customer = COUNT(Orders[Customer Name])

Now we will select the stacked column chart visual from the visualization pane.
In the axis field, drag and drop the region column from the field pane.
In the value field, drag and drop the sales from the field pane.
In the tooltip field, drag and drop the customer measure from the field pane.

Click on the more icon(…) in the canvas, select sort by -> Customer( choose the measure).
Then you can sort in ascending or descending order the stacked column chart by using measure.

Now in the below screenshot, you can see the we have sorted the measure in the descending order without showing in the visual.

Read Power Bi Pyramid chart – How to use

Power bi sort by measure not in table

Here we will see how to sort the measure in table visual without showing measure in power bi.

We will use the same order sample data and the same customer measure which we have used in the just above topic.

Here we will create a table visual, then we will add two fields i.e Region and sales, and also we will add the customer measure.

Then we will hide the measure from the table, and then we will sort the table visual by using the measure.

Load the data using get data.
Click on the table visual, from the visualization pane.
In the value field, drag and drop the region, sales and customer measure from the field pane.

Now let’s hide the customer measure from the table visual by dragging the column width of the measure from right to left and make it very small.

Next drag the border of the table visual until it hide the measure column.

Now click on more icon(…) in the canvas to sort the table visual by using customer measure.
Then click on sort by -> Customer, and now you can sort it by ascending or descending as per your requirement, here i will sort it by descending order.

Now in the below screenshot, you can see the table visual is sorted in descending order, without including in the table visual.

Read Power Bi Bar Chart

Power bi sort by measure by another column

Here we don’t need any measure to sort the column by another column, we can use the in-built sort by column feature in the power bi desktop.

Here we will use the financial sample data, in this sample data we have the month name column which is alphabetically ordered. For example April, August, December, February, January, July, and so on.

So, we want them to be sorted chronologically, for this we have one more column i.e. Month number.

Now, we can sort the order of one column by another column, but both the column need to be at the same level of granularity.

Load the data using get data.
Here i have already created a line chart, which shows sales by month. You can see the month name are sorted alphabatically.

Power bi sort by measure by another column Power bi slicer sort by measure — Power bi sort by measure by another column Power bi slicer sort by the measure

Now we will see how to sort the Month name column by Month number column.

Select the month name column from the field pane.
In the ribbon column tools will open, here click on the sort by column icon.
Then select Month Number from the list.

Now you can see sort one column by another column in power bi desktop.

Read Power Bi line chart

Power bi slicer sort by measure

Here we will see how to sort a measure by topN both ascending and desc order using slicer.

We have a sample data table that contains name column and points column.

Next, we will create a slicer table, which contains a sort column with Top 10 and Bottom 10 using the measure.

When we click on Top 10 in slicer visual, it will show the top 10 points in descending order and when we click on the Bottom 10, it will show the bottom 10 points in Ascending order.

We will use the below slicer table.

And also we will use this measure in the filter to show only 10 points in the visual, whether you select Top 10 or bottom 10 in the slicer.

Lets load the data using get data in power bi desktop.
Click on new measure from the ribbon.
Then write the below measure:

Rank = 
IF (
    HASONEVALUE ( View[View] ),
    SWITCH (
        VALUES ( View[View] ),
        "Top 10", RANKX ( ALL ( 'Table' ), CALCULATE ( SUM ( 'Table'[Points] ) ),, DESC, DENSE ),
        RANKX ( ALL ( 'Table' ), CALCULATE ( SUM ( 'Table'[Points] ) ),, ASC, DENSE )
    )
)

Now to check the measure, select a stacked column chart from the visualization pane.
In the axis field, drag and drop the name column from the field pane.
In the value field, drag and drop the value column from the field pane.

Now we will create a slicer visual, so click on the slicer visual from the visualization pane.
In the field, drag and drop the view column from the View table in the field pane.

To show only 10 data in stacked column chart, we will filter the measure.
In the filter pane, add the measure to the field under visual level filter.
The select is less than or equal to 10, click on apply filter.

Now select bottom 10 in the slicer, it will show the bottom 10 points in the stacked column chart.

Power bi sort legend by measure

As we know in Power bi legend field does not accept the measure, so we cannot sort the legend by using the measure.

Instead of Measure, we can use the Custom column in Power Bi to sort the legend.

You may like the following Power BI tutorials:

In this Power bi tutorial, we learned Power bi sort by measure with examples. And also we discuss the below points:

Sort by measure power bi
Sort column by measure power bi
Sort matrix by measure power bi
Power bi sort by measure not in visual
Power bi sort by measure not in table
Power bi sort by measure by another column
Power bi slicer sort by measure
Power bi sort legend by measure

Bhawana Rathore

Bhawana Rathore is a Microsoft MVP (3 times in Office Apps & Services) and a passionate SharePoint Consultant, having around 10 years of IT experience in the industry, as well as in .Net technologies. She likes to share her technical expertise in EnjoySharePoint.com and SPGuides.com