Change sort order based on measure selection

[Shinu1]

Hi,

I am not sure how to change the sort order based  a measure selection. 

I have a matrix table showing measure based on the selection from a slicer. My problem is with the sorting order based on selection.

Example :

Slicer                       Matrix Table

ER                            Accounts    Total

CM                           a                 100

TC                            b                   40

TR                            c                   90

When I select "ER" or  "TR" from slicer, i want the table to sort the measure in Descending order and on "CM" and "TC" selection, the sorting order should change to Ascending order

Please advise how can i achieve it.

Thanks

Shinu

[mhossain]

Hi @Shinu1 

Try below, I don't know your table names, "tbl_Processed_Rawdata" you can try "ALL" in below dax, basically we want to clear the slicer selection while calculating the rank, ranks should be based on all the KPIs, so adjust below measures or provide me the snapshot of sample data, hope this makes sense.

Rank Measure =

VAR selected =
SELECTEDVALUE ( tbl_KPI[KPI] )
VAR rankER = RANKX(All(tbl_Processed_Rawdata),[ExternalRevenue],,DESC)
VAR rankCM = RANKX(All(tbl_Processed_Rawdata),[Contribution Margin],,ASC)
VAR rankTC = RANKX(All(tbl_Processed_Rawdata),[Total Cost],,ASC)
VAR rankTR = RANKX(All(tbl_Processed_Rawdata),[Total Revenue],,DESC)
RETURN
SWITCH ( selected, "ER", rankER, "CM", rankCM, "TC", rankTC, "TR", rankTR )

[parry2k]

@Shinu1 I'm afraid that you can change the sort order based on the selection. What is the use case that you need to change the sort order, maybe there is another solution.

[Shinu1]

Different measures are looked with different perspective.

Example: The Cost line items are all in negative and hence the highest negative amount needs to pulled at the top in matrix table and viceversa for Reveue/Profit.

Hope I'm clear

Thanks

Shinu

[parry2k]

@Shinu1 I didn't see any negative value in your sample dataset and that's why it was not clear. 

[parry2k]

@Shinu1 only solution I can think of is to create calculated table and use sorting option.

[Shinu1]

Based on Slicer selection, different measures populate data in the table. One measure might populate positive amount and the other measure might populate negative value. I'm using Switch formula to check slicer selection and then show the respective measures data.

Switch formula:

mKPI = SWITCH(ALLSELECTED(tbl_KPI[KPI]),"ER",[ExternalRevenue],"TR",[Total Revenue],"TC",[Total Cost],"CM",[Contribution Margin],0)

Below are two of the measures used in above Switch statement measure

ExternalRevenue= CALCULATE(SUM(tbl_Processed_Rawdata[External Revenue]))

Total Cost = CALCULATE(SUM(tbl_Processed_Rawdata[Total Cost]))

I need to sort External Revenue in Descending order(Largest to smallest) and Total Cost in Ascending order (Smallest to lowest)

I 'm not sure how to fit in sort parameter in Calculatedtable to see the output in matrix table. If you could give me a small example to fit in the sort parameter, i can give a try to it.

Thanks

Shinu

[v-shex-msft]

Hi @Shinu1,

Currently, you can't directly use a slicer to affect with matrix fields sort order. For indirect methods, I'd like to suggest you write a measure with multiple ranking expressions, then you can nested them with if statement condition with slicer selections. 

After you add this field into your visual and setup sort by measure value, it can dynamic changes based on slicer selections.

Rank Measure =
VAR selected =
    SELECTEDVALUE ( tbl_KPI[KPI] )
VAR rankER = 'rank formula based on value and its sort order type'
VAR rankCM = 'rank formula based on value and its sort order type'
VAR rankTC = 'rank formula based on value and its sort order type'
VAR rankTR = 'rank formula based on value and its sort order type'
RETURN
    SWITCH ( selected, "ER", rankER, "CM", rankCM, "TC", rankTC, "TR", rankTR )

Regards,

Xiaoxin Sheng

[Shinu1]

Thanks for the reply. I tried as you instructed. Below is the screenshot fo the same. The matrix table now has two columns for each header in Columns section  and the rank measure is ranking everything as 1.

Below is the Rank Measure:

Rank Measure =

VAR selected =

    SELECTEDVALUE ( tbl_KPI[KPI] )

VAR rankER = RANKX(tbl_Processed_Rawdata,[ExternalRevenue],,DESC)

VAR rankCM = RANKX(tbl_Processed_Rawdata,[Contribution Margin],,ASC)

VAR rankTC = RANKX(tbl_Processed_Rawdata,[Total Cost],,ASC)

VAR rankTR = RANKX(tbl_Processed_Rawdata,[Total Revenue],,DESC)

RETURN

    SWITCH ( selected, "ER", rankER, "CM", rankCM, "TC", rankTC, "TR", rankTR )

Please advise what needs to be corrected.

Thanks

Shinu

[Shinu1]

Hi,

let me know if anyone has any solution for the same.

Thanks

Shinu