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)