Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I am trying to create a matrix visual but unable to do so. Can someone pl. help
this is what I am able to create
City | 2020 | 2021 | 2022 | ||||||
Sales | profit | Revenue | Sales | profit | Revenue | Sales | profit | Revenue | |
New York | 61 | 66 | 96 | 100 | 100 | 61 | 98 | 95 | 84 |
Los Angeles | 86 | 63 | 73 | 53 | 66 | 53 | 98 | 81 | 80 |
Chicago | 94 | 95 | 77 | 68 | 63 | 66 | 62 | 83 | 64 |
Houston | 100 | 96 | 57 | 57 | 66 | 65 | 98 | 69 | 73 |
Phoenix | 90 | 63 | 84 | 57 | 67 | 58 | 74 | 97 | 50 |
Philadelphia | 54 | 77 | 75 | 71 | 73 | 55 | 64 | 93 | 83 |
San Antonio | 70 | 85 | 61 | 95 | 97 | 98 | 80 | 57 | 95 |
San Diego | 98 | 86 | 70 | 99 | 78 | 54 | 64 | 62 | 63 |
Dallas | 62 | 69 | 78 | 51 | 97 | 79 | 78 | 56 |
68
|
But this is what i want
Sales | Sales | Sales | profit | profit | profit | Revenue | Revenue | Revenue | |
City | 2020 | 2021 | 2022 | 2020 | 2021 | 2022 | 2020 | 2021 | 2022 |
New York | 61 | 100 | 98 | 66 | 100 | 95 | 96 | 61 | 84 |
Los Angeles | 86 | 53 | 98 | 63 | 66 | 81 | 73 | 53 | 80 |
Chicago | 94 | 68 | 62 | 95 | 63 | 83 | 77 | 66 | 64 |
Houston | 100 | 57 | 98 | 96 | 66 | 69 | 57 | 65 | 73 |
Phoenix | 90 | 57 | 74 | 63 | 67 | 97 | 84 | 58 | 50 |
Philadelphia | 54 | 71 | 64 | 77 | 73 | 93 | 75 | 55 | 83 |
San Antonio | 70 | 95 | 80 | 85 | 97 | 57 | 61 | 98 | 95 |
San Diego | 98 | 99 | 64 | 86 | 78 | 62 | 70 | 54 | 63 |
Dallas | 62 | 51 | 78 | 69 | 97 | 56 | 78 | 79 | 68 |
in the above matrix I am using Sales, Profit and Revenue measures in the 1st column header and second column header is year. i have provided sample data above as well.
thanks in advance.
Proud to be a Super User!
Solved! Go to Solution.
A couple of solutions, depending on how yo want to depict the columns.
The model is set up as follows:
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
Proud to be a Super User!
Paul on Linkedin.
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.
Please provide sample data in usable (unpivoted) format.
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 |
Proud to be a Super User!
@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.
Proud to be a Super User!
A couple of solutions, depending on how yo want to depict the columns.
The model is set up as follows:
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
Proud to be a Super User!
Paul on Linkedin.
@PaulDBrown thank you for your detailed response. You have given more than enough ways to tackle this problem.
Proud to be a Super User!
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |