Change sort order based on measure selection

Question

Hi,I am not sure how to change the sort order based&nbsp; a measure selection.&nbsp;I have a matrix table showing measure based on the selection&nbsp;from a slicer. My problem is with the sorting order based on selection.&nbsp;Example :&nbsp;Slicer&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Matrix TableER&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Accounts&nbsp; &nbsp; TotalCM&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;a&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;100TC&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; b&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;40TR&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; c&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;90&nbsp;When I select "ER" or&nbsp; "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&nbsp;Please advise how can i achieve it.&nbsp;ThanksShinu&nbsp;&nbsp;&nbsp;

parry2k · Answer

Shinu1&nbsp;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 · Answer

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 · Answer

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

parry2k · Answer

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

shinu1 · Answer

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)&nbsp;Below are two of the measures used in above Switch statement measureExternalRevenue= CALCULATE(SUM(tbl_Processed_Rawdata[External Revenue]))Total Cost = CALCULATE(SUM(tbl_Processed_Rawdata[Total Cost]))&nbsp;I need to sort External Revenue in Descending order(Largest to smallest) and Total Cost in Ascending order (Smallest to lowest)&nbsp;I&nbsp;'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.&nbsp;&nbsp;ThanksShinu

Forum Discussion

Change sort order based on measure selection

9 Replies

Related content

Change in category selection changes selected subcategory in slicers

Bookmarks that changes the measure selected

Drive / change slicer selection based on value selected from a matrix

DAX Measure Not Showing Correct Slicer State (All Selected, No Selection, Partial Selection)

Slicer selection button color change using native visual