Matrix Visual - Custom Sort Columns

[ryan_b_fiting]

Hello Community - 

I have a Power BI Report that the main analyses being done are in a matrix visual.  The Matrix allows you to select a Main Facility (from the SimPLSegment table) and then multiple comparison companies (DimPLSegmentComps table).  When I choose to Comp Companies, I also put the main company in that selection as well.  I have a measure that calculate the average of all the comps (excluding the main facility selected) rather than a sum so we can compare vs individual comps but also compare vs the average of all of those comps.

Display Amount = 
VAR _MyFacility = VALUES(DimPLSegment[FacilityID])
VAR _FilteredComps = VALUES(DimPLSegmentComps[FacilityID])
VAR _CompSet = EXCEPT(_FilteredComps, _MyFacility)
VAR _IncludedFacilities = 
FILTER( 
    ALL(DimPLSegmentComps), 
    DimPLSegmentComps[FacilityID] IN _CompSet && NOT(ISBLANK(DimPLSegmentComps[FacilityID])) 
)
VAR _FacilityCount =
    CALCULATE(
        DISTINCTCOUNT(DimPLSegmentComps[FacilityID]),
        _IncludedFacilities
    )
VAR _TotalCompActual =
    CALCULATE(
        [Comp Actual Amount],
        _IncludedFacilities
    )
VAR CompAvg = DIVIDE(_TotalCompActual, _FacilityCount)

RETURN
IF(
    DISTINCTCOUNT(DimPLSegmentComps[FacilityID]) = 1,
    COALESCE([Comp Actual Amount], 0),
    CompAvg
)

 What I need to be able to do in my matrix is when I choose my Main Facility, I ALWAYS need that facility to be displayed as the first facility in the Matrix columns and then my comps that follow are fine to go in alpha order.  How can I get this custom sorting in a matrix based on a Main selected facility?

I have am active relationship between DimPLSegmentComps and the FactTransactions table and an INACTIVE relationship between DimPLSegments and FactTransaction table.

Any help would be greatly appreciated, community.  I have spent a few days trying to figure out a solution, but have had no luck.

Thank you community,

Ryan F 

[v-sgandrathi]

Hi @ryan_b_fiting,

Checking in to confirm if your concern has been addressed. If the reply helped, please consider accepting it as the solution for the benefit of other users.

Thank you.

[v-sgandrathi]

Hi @ryan_b_fiting,

Thank you for testing and sharing the screenshot. In the Matrix visual, sorting by a measure in the Values area changes the order of the rows only, not the columns. Columns are sorted by the field placed in the Columns well and its sort settings, so a rank measure can’t move your selected MAIN facility to the first column dynamically.

Currently, there isn’t a built-in way to always display the selected facility first in the Matrix columns. As a workaround, you could put facilities on the rows and use the rank measure for sorting, or try a different visual/layout if you need the “selected facility always first” feature.

If dynamic column ordering is important for your scenario, you may want to submit this idea in the Fabric Ideas forum for the product team to consider.
Fabric Ideas - Microsoft Fabric Community

Thank you 
sahasra.

[parry2k]

@ryan_b_fiting could you first create the rank measure as I described before, and let's go from there? Basically, this rank measure you will drop in the values section and click on the rank header in the matrix visua to sort the matrix by this column. 

if it is still not clear, it will be easier if you share pbix file using one drive/google drive  Remove any sensitive information before sharing.

[ryan_b_fiting]

Hey @parry2k  I tried using the ranking logic and sorting on that 'value', but it does not work.  I am still not understanding how adding a measure to the 'values' will sort the columns on the matrix I guess.

[v-sgandrathi]

Hi @ryan_b_fiting,

Thankyou @parry2k for your response to the query.

You can set a custom column order in your matrix by creating a ranking logic that gives the main facility chosen in your slicer a lower rank, ensuring it appears first. Assign higher ranks to the other comparison facilities. After creating this ranking measure, add it to your matrix and sort the facility column in ascending order. This will place the main facility at the top, with the others following in alphabetical or numerical order. You can reduce the ranking column's width or hide it to keep your matrix layout clean. This method maintains your comparison logic and provides a clear display order.

Thank you.

[ryan_b_fiting]

@parry2k @v-sgandrathi thanks for the reply, but this is not the solution I am looking for.  I am not looking to sort the rows of data in a matrix, I am looking to sort the columns.  In my experience I have never been able to put a measure in the 'columns' section of a matrix.  So this is essentially what I need to do:
FAC-027948 is selected as my MAIN FACILITY.  It highlights that row with condiditonal formatting, what I need to do is have that MAIN FACILITY be the first column, in front of FAC-027752.  And then as I change my MAIN FACILITY, whichever on is selected, that need to go to the first column, next to the accounts.

[parry2k]

@ryan_b_fiting @As mentioned previously there is no way to do the dynamic sorting without adding a measure to create the sort order. 

[ryan_b_fiting]

Thanks @parry2k appreciate the response.  I guess I am not understanding how that rank measure would even work to sort the columns in order?

[parry2k]

@ryan_b_fiting, what you need to do is to create a rank measure of your selected companies and always give rank 0 to your main company, and then use this rank in the matrix visual and sort by this rank measure. You can make the column smaller enough (kinda hide) to not to show the rank.

Hope this helps.