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
below is the data. hope this can help
| City | Sales | Year | City | Profit | Year | City | Revenue | Year | ||
| New York | 61 | 2020 | New York | 66 | 2020 | New York | 96 | 2020 | ||
| New York | 100 | 2021 | New York | 100 | 2021 | New York | 61 | 2021 | ||
| New York | 98 | 2022 | New York | 95 | 2022 | New York | 84 | 2022 | ||
| Los Angeles | 86 | 2020 | Los Angeles | 63 | 2020 | Los Angeles | 73 | 2020 | ||
| Los Angeles | 53 | 2021 | Los Angeles | 66 | 2021 | Los Angeles | 53 | 2021 | ||
| Los Angeles | 98 | 2022 | Los Angeles | 81 | 2022 | Los Angeles | 80 | 2022 | ||
| Chicago | 94 | 2020 | Chicago | 95 | 2020 | Chicago | 77 | 2020 | ||
| Chicago | 68 | 2021 | Chicago | 63 | 2021 | Chicago | 66 | 2021 | ||
| Chicago | 62 | 2022 | Chicago | 83 | 2022 | Chicago | 64 | 2022 | ||
| Houston | 100 | 2020 | Houston | 96 | 2020 | Houston | 57 | 2020 | ||
| Houston | 57 | 2021 | Houston | 66 | 2021 | Houston | 65 | 2021 | ||
| Houston | 98 | 2022 | Houston | 69 | 2022 | Houston | 73 | 2022 | ||
| Phoenix | 90 | 2020 | Phoenix | 63 | 2020 | Phoenix | 84 | 2020 | ||
| Phoenix | 57 | 2021 | Phoenix | 67 | 2021 | Phoenix | 58 | 2021 | ||
| Phoenix | 74 | 2022 | Phoenix | 97 | 2022 | Phoenix | 50 | 2022 | ||
| Philadelphia | 54 | 2020 | Philadelphia | 77 | 2020 | Philadelphia | 75 | 2020 | ||
| Philadelphia | 71 | 2021 | Philadelphia | 73 | 2021 | Philadelphia | 55 | 2021 | ||
| Philadelphia | 64 | 2022 | Philadelphia | 93 | 2022 | Philadelphia | 83 | 2022 | ||
| San Antonio | 70 | 2020 | San Antonio | 85 | 2020 | San Antonio | 61 | 2020 | ||
| San Antonio | 95 | 2021 | San Antonio | 97 | 2021 | San Antonio | 98 | 2021 | ||
| San Antonio | 80 | 2022 | San Antonio | 57 | 2022 | San Antonio | 95 | 2022 | ||
| San Diego | 98 | 2020 | San Diego | 86 | 2020 | San Diego | 70 | 2020 | ||
| San Diego | 99 | 2021 | San Diego | 78 | 2021 | San Diego | 54 | 2021 | ||
| San Diego | 64 | 2022 | San Diego | 62 | 2022 | San Diego | 63 | 2022 | ||
| Dallas | 62 | 2020 | Dallas | 69 | 2020 | Dallas | 78 | 2020 | ||
| Dallas | 51 | 2021 | Dallas | 97 | 2021 | Dallas | 79 | 2021 | ||
| Dallas | 78 | 2022 | Dallas | 56 | 2022 | Dallas | 68 | 2022 |
- negi0074 years agoCommunity Champion
lbendlin thank you for your response. in my case problem is that all these are seperate tables and without combining them I wish to have same result. Pl. tell me if that is possible.
- PaulDBrown4 years agoCommunity Champion
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
- negi0074 years agoCommunity Champion
PaulDBrown thank you for your detailed response. You have given more than enough ways to tackle this problem.