Forum Discussion
matrix rows order
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 🙂
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.
6 Replies
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.
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
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.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.
Hi FZOU,
Checking in to see if your issue has been resolved. let us know if you still need any assistance.
Thank you.
