Sorting Matrix by columns

Hi
I have a matrix table that gives info by month

The user wants to be able to click on a column for a given month and sort

The sort option on the matrix only seems to sort based on the totals of the columns
image

Is there a way to change this? ie if the user clicks on # Transactions for Month 9 it would sort descending?
I’ve attached a pbix file

Sorting by month column - Copy.pbix (858.1 KB)

Thank you

1 Like

It seems I can only get this to work if I add a slicer for the month thus meaning the total column is only for that month

Hi @AliB,
Thank you very much for posting your query in the forum.
I share a possible solution in which the user has to select the Month and the Measure by which he wants to order the matrix.

You can look at the following post which I have followed to implement it.
Sort matrix by selected columns with RankX – Alintelligence

I have created the following tables:

  • Month Sort: Month selected for sorting
  • PrmMeasures: Measure selected for sorting

And the following measures in the Report Measures folder:

Sort =

VAR _SelectedMonth = SELECTEDVALUE( 'Month Short'[Value] )

VAR _SelectedMeasure = MAX( PrmMeasures[PrmMeasures] )

VAR _Result = SWITCH(

    TRUE(),

    _SelectedMeasure = "# Transactions", RANKX(

        ALL( Clients[Ac No] ),

        CALCULATE(

            [# Transactions],

            Dates[Month] = _SelectedMonth

        ),,

        ASC,

        Dense

    ),

    _SelectedMeasure = "£ Transactions", RANKX(

        ALL( Clients[Ac No] ),

        CALCULATE(

            [£ Transactions],

            Dates[Month] = _SelectedMonth

        ),,

        ASC,

        Dense

    ),

    _SelectedMeasure = "Rolling Avg", RANKX(

        ALL( Clients[Ac No] ),

        CALCULATE(

            [Rolling Avg],

            Dates[Month] = _SelectedMonth

        ),,

        ASC,

        Dense

    ),

    _SelectedMeasure = "% Rev to Transaction", RANKX(

        ALL( Clients[Ac No] ),

        CALCULATE(

            [% Rev to Transaction],

            Dates[Month] = _SelectedMonth

        ),,

        ASC,

        Dense

    )

)

RETURN

IF( ISINSCOPE( Clients[Ac No] ), _Result )
Highlight Color #Transactions =

VAR _SelectedMonth = SELECTEDVALUE( 'Month Short'[Value] )

VAR _CurrentMonth = SELECTEDVALUE( Dates[Month] )

VAR _SelectedMeasure = MAX( PrmMeasures[PrmMeasures] )

RETURN

IF(

    _SelectedMeasure = "# Transactions" &&

    _SelectedMonth = _CurrentMonth,

    "#E4EEF4", ""

)
Highlight Color %RevtoTrasaction =

VAR _SelectedMonth = SELECTEDVALUE( 'Month Short'[Value] )

VAR _CurrentMonth = SELECTEDVALUE( Dates[Month] )

VAR _SelectedMeasure = MAX( PrmMeasures[PrmMeasures] )

RETURN

IF(

    _SelectedMeasure = "% Rev to Transaction" &&

    _SelectedMonth = _CurrentMonth,

    "#E4EEF4", ""

)
Highlight Color £Transactions =

VAR _SelectedMonth = SELECTEDVALUE( 'Month Short'[Value] )

VAR _CurrentMonth = SELECTEDVALUE( Dates[Month] )

VAR _SelectedMeasure = MAX( PrmMeasures[PrmMeasures] )

RETURN

IF(

    _SelectedMeasure = "£ Transactions" &&

    _SelectedMonth = _CurrentMonth,

    "#E4EEF4", ""

)
Highlight Color RollingAvg =

VAR _SelectedMonth = SELECTEDVALUE( 'Month Short'[Value] )

VAR _CurrentMonth = SELECTEDVALUE( Dates[Month] )

VAR _SelectedMeasure = MAX( PrmMeasures[PrmMeasures] )

RETURN

IF(

    _SelectedMeasure = "Rolling Avg" &&

    _SelectedMonth = _CurrentMonth,

    "#E4EEF4", ""

)

TransparentColor = "#FFFFFF00"

I hope it can be of help to you.
Regards

Sorting by month column_JAFP.pbix (863.1 KB)

Hi @AliB,
Have you been able to try the soluction I shared?

Regards

Hello @AliB

Did the response from @jafernandezpuga help solve your query?

If not, can you let us know where you’re stuck and what additional assistance you need?

If it did, please mark his answer as the SOLUTION.

Thank you

Hi @AliB ,

Due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.

Thanks to @jafernandezpuga for contributing to this post.

We request you to kindly take time to answer the Enterprise DNA Forum User Experience Survey,

We hope you’ll give your insights on how we can further improve the Support forum. Thanks!

1 Like