Forum Discussion
Matrix - Multiple Column Header Order
- 4 years ago
A couple of solutions, depending on how yo want to depict the columns.
The model is set up as follows:
Dimension tables for City and Year
Create a new table to use as the matrix headers using:
Header = VAR Metric = { ( "Sales", 1 ), ( "Profit", 2 ), ( "Revenue", 3 ) } RETURN CROSSJOIN ( Metric, 'Year table' )Set up a relationdhip between the Year dimension table and the Year field in the header table as follows:
1) The simple solution:
Now, based on simple SUM measures por sales, profit and revenue, create the measure to use in the matrix using:
Matrix Value = SWITCH ( SELECTEDVALUE ( Header[Order] ), 1, [Sum Sales], 2, [Sum Profit], 3, [Sum Revenue] )Set up the matrix using the Year field from the Year Dimension table, the Metric field from the Header Table, the city field from the dimension table and the [Matrix Value] measure to get:
2) the more detailed solution
If you actually want the column headers as you depicted in the example, you need to go further. Create two new calculated columns in the Header table:
Matrix Columns = Header[Metric] & " " & Header[Year]and this column to sort the Matrix columns by:
Sort MC = Header[Order] * 10000 + Header[Year]Change the measure for the matrix to:
Matrix Value 1 = SWITCH ( SELECTEDVALUE ( Header[Order] ), 1, CALCULATE ( [Sum Sales], TREATAS ( VALUES ( Header[Year] ), 'Year table'[Year] ) ), 2, CALCULATE ( [Sum Profit], TREATAS ( VALUES ( Header[Year] ), 'Year table'[Year] ) ), 3, CALCULATE ( [Sum Revenue], TREATAS ( VALUES ( Header[Year] ), 'Year table'[Year] ) ) )and finally create the matrix using the Matrix Column field as the columns:
I've attached the sample PBIX file
Step 1
First of all, your data need to be in following shape. Then you have to add a custom column as mentioned in the snapshot.
Step 2
Create a custom table like below and also add a custom column as marked below. Set sort order of sections to column "Sqc"
Step 3
Create relationship between two tables by linking custom columns.
Step 4
Create following set of measures.
Step 5
Use all columns and measures in matrix as shown below. Switch off column sub totals. Then expand to the lowest level on visual header to bring it to following form.
Note:
To make subtotals work, you have to create additional measures. The above example will cover your data arrangement requirements. Hope it satisfies your need.