Sort Column Matrix by a calculated measure

[LUCASM]

I have a Matrix with 2 column headers:

Country and

Year

In values I have 3 rows:

Sum of Sales (Ths.),

Year on Year % Change (+/- % PY) and

Country Share % (Horiz. %)

Test pbix file 

I am trying to create a Rank of Country by the Measure, so that I can sort my Country Headers in order of Country Share Greatest to Least, Like this.

I have tried 

Rank =

RANKX (

    ALL (

        'Table'[Country] ),

        [Horiz. %],,DESC,Dense

        )

But that gives me 1 for everything

[LUCASM]

Hi @SamInogic 
After completely niss seeing what you so cometantly explained, it suddenly dawned on me what you were doing.
The Rank measure is required in the Matrix and then sort Country by the Row Rank measure
yes I see that works now and I can hide this row in a variaty of ways.

many thanks, I learnt a lot.

[SamInogic · Accepted solution]

Hi,

The issue where your RANKX measure returns 1 for every country is likely due to the context in which your Horiz. % measure is being evaluated within the matrix. Power BI can sometimes struggle to maintain the correct context when dealing with multi-level columns or matrices.

Here are the steps to correct this:

1. Adjust Your RANKX Measure

When using a Matrix with multi-level column headers, you need to ensure that the ranking measure is calculating in the correct context of the country, ignoring the year but respecting any filters on the matrix.

Modify your Rank measure like this:

DAX

Copy code

Rank =

RANKX (

    ALLSELECTED('Table'[Country]), // Ensure that the ranking applies to all selected countries

    CALCULATE([Horiz. %]),         // Calculate the measure in the correct context

    ,                               // Optional: if you want to include a tie-breaking expression, add it here

    DESC,                           // Sort in descending order

    DENSE                           // Dense ranking, meaning no gaps in rank numbers

)

• ALLSELECTED: This ensures that any slicers or filters applied to the matrix are respected (e.g., filtering by year).
• CALCULATE([Horiz. %]): Forces the calculation of the Horiz. % measure for each country.
• DESC, DENSE: Sorts the values from highest to lowest and applies a dense rank (no skipped numbers in rank).

2. Add the Rank Measure to the Matrix

• After creating the Rank measure, make sure to add it to your matrix (or a test table) to see if the ranking works as expected.

3. Sort by the Rank Measure

• To get the countries sorted in descending order of Horiz. %, go to the Country header in your matrix and sort it by the Rank measure.
• You can do this by clicking on the Sort by Column option in the Power BI ribbon, selecting Country, and then choosing Rank as the sorting column.

4. Filtering for Specific Years (Optional):

If you're ranking based on a specific year's Horiz. %, you may need to adjust your rank measure to focus on the selected year:

DAX

Copy code

Rank =

RANKX (

    ALLSELECTED('Table'[Country]),

    CALCULATE([Horiz. %], 'Table'[Year] = SELECTEDVALUE('Table'[Year])),

    ,

    DESC,

    DENSE

)

This ensures the ranking is based on the selected year's Horiz. %, not the overall total.

5. Matrix Column Headers Sorting:

Once the rank measure is correctly calculating, apply sorting by Rank in the matrix by doing the following:

• Select the column header for Country.
• In the Column Tools section, choose Sort by Column and select the Rank measure.
  
  

Additional Step (Optional):

If you want to rank countries only for a specific year (e.g., 2024), consider adding a filter or slicer for the year in the Rank calculation. You can add a slicer for Year in your Power BI report or modify the Rank formula further:

DAXCopy codeRank = RANKX(    ALL('Table'[Country]),    CALCULATE([Horiz. %], 'Table'[Year] = SELECTEDVALUE('Table'[Year])),    ,    DESC,    DENSE)

This should fix the issue, and the countries will be sorted by their Horiz. % share in descending order. If you are still seeing 1 for all ranks, double-check the context of your Horiz. % measure and ensure it's calculating correctly for each country.

Hope this helps.