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
Please provide sample data in usable (unpivoted) format.
- negi0074 years agoCommunity Champion
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 - lbendlin4 years agoSuper User