matrix rows order

[FZOU]

Hello Community,

In a Power BI Matrix, the region (France, EMEA, Japan, etc.) is always shown above the list of stores, because the visual enforces hierarchical ordering like shown below : 

I need the opposite layout: store, and the the region below them, like this:

  paris1

  paris2

  paris3

FRANCE

  Milan

EMEA

but when i swap region and store in power bi i got this : 
paris1

france

paris2

france

paris3

france

any one have an idea please ? thank you 🙂 

[v-saisrao-msft]

Hi @FZOU,

Checking in to see if your issue has been resolved. let us know if you still need any assistance.

Thank you.

[v-saisrao-msft]

Hi @FZOU,

Have you had a chance to review the solution we shared by @rohit1991 @Arul @Ahmed-Elfeel @Praful_Potphode ? If the issue persists, feel free to reply so we can help further.

Thank you.

[Praful_Potphode]

Hi @FZOU ,

when we need our own sorting order, it is recommeneded to create disconnected table specifying the order(in your case store and region), then show the output using selectedvalue dax function.

Please give kudos or mark it as solution once confirmed.

Thanks and Regards,

praful

[Ahmed-Elfeel · Accepted solution]

Hi @FZOU,

Power BI matrix visual is designed for parent child hierarchies so it repeats the parent (Region) for each child (Store) when you swap them...So here is a couple of solutions Should work try it:

First Approach:

In this approach you will create a custom display column that controls the exact order of stores and regions

• Create a Sorting Index Column:

SortIndex = 
VAR CurrentStore = [Store]
VAR CurrentRegion = [Region]
VAR StoreRank = RANKX(
    FILTER(ALL('Stores'), [Region] = CurrentRegion),
    [Store],
    ,
    ASC,
    DENSE
)
RETURN
StoreRank * 1000  -- Stores get values like 1000, 2000, 3000

• Then Create Display Column:

StoreRegionDisplay = 
VAR CurrentStore = [Store]
VAR CurrentRegion = [Region]
VAR IsRegionRow = [Store] = [Region]  -- Assuming you have region rows in table
RETURN
IF(
    IsRegionRow,
    UPPER(CurrentRegion),  -- Region in uppercase
    CurrentStore           -- Store as normal
)

• Also Create Region Sort Column:

RegionSort = 
VAR CurrentRegion = [Region]
VAR RegionNumber = SWITCH(
    CurrentRegion,
    "FRANCE", 1,
    "EMEA", 2,
    "CHINA", 3,
    "ARANI", 4,
    99
)
RETURN RegionNumber

• Finally Create Final Sorting Column:

FinalSort = 
[RegionSort] * 10000 + [SortIndex]

Note:

• Sort StoreRegionDisplay by FinalSort

• Use StoreRegionDisplay in matrix rows

Second Approach:

Here we will use measures to dynamically display stores and regions

• Just Create a Store List Measure:

StoreList = 
VAR StoresInContext = VALUES('Stores'[Store])
VAR CurrentRegion = SELECTEDVALUE('Stores'[Region])
VAR StoreCount = COUNTROWS(StoresInContext)
RETURN
IF(
    StoreCount = 1,
    SELECTEDVALUE('Stores'[Store]),
    CurrentRegion & " - " & FORMAT(StoreCount, "0") & " stores"
)

• Then Use It in Matrix

  • Rows: Store field

  • Add this measure to get smart display

Bonus Approach: 

We will Just create Unpivot table then use it in your Custome Visual

• Create unpivoted table:

DisplayTable = 
UNION(
    SELECTCOLUMNS(
        FILTER('Stores', [Store] <> [Region]),
        "DisplayType", "Store",
        "DisplayValue", [Store],
        "Region", [Region],
        "SortOrder", 1
    ),
    SELECTCOLUMNS(
        'Stores',
        "DisplayType", "Region",
        "DisplayValue", [Region],
        "Region", [Region],
        "SortOrder", 2
    )
)

• Then use it in custom visual or table

if this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.

[Arul]

@FZOU ,

Power BI’s Matrix visual enforces a hierarchical drill down structure and always places parent categories above child categories. Reversing the hierarchy causes the parent to repeat for each child row. The visual does not support placing a parent category below its children.

[rohit1991]

Hii @FZOU 

Matrix visuals always follow the hierarchy order defined in your data model, so you cannot place “Store” above “Region” unless Region is turned into a value instead of a grouping level. To get the layout you want, create a flattened table where each store is listed once and Region is just an attribute, then put Store in Rows and Region in Values or a separate visual. Power BI cannot reverse a hierarchy inside the Matrix, so the only workaround is to break the hierarchy and treat Region as a normal column rather than a parent level.