Forum Discussion
Matrix Table
Hello.
I have a main table and a date table. The main table has sales by 2 companies. The date table has fiscal months and related to the main table through the DATE column.
MAIN TABLE
| DATE | CUSTOMER | SALES | DEPT. |
| Nov. 1, 2021 | ABC | 25 | A |
| Nov. 2, 2021 | XZY | 50 | B |
| Dec. 5, 2022 | ABC | 35 | A |
Using Matrix table, this is the result that I am getting
| CUSTOMER | DEPT. A | DEPT. A | DEPT. B | DEPT. B | TOTAL (LY) | TOTAL (CY) |
| NOV. (LY) | NOV. (CY) | NOV. (LY) | NOV. (CY) | |||
| CUST. ABC | 200 | 100 | 500 | 300 | 700 | 400 |
However, I would like to present the month to month comparison between current year (CY) and last year (LY) so that the matrix table will show like this.
| CUSTOMER | DEPT. A | DEPT. B | TOTAL (LY) | DEPT. A | DEPT. B | TOTAL (CY) | DIFFERENCE (CY)-(LY) |
| (LY) NOV | (LY) NOV | (CY) NOV | (CY) NOV | ||||
| CUST. ABC | 200 | 500 | 700 | 100 | 300 | 400 | -300 |
Here is the table and matrix illustration:
Any help is highly appreciated. Thanks.
Oros , the Calculation group can help measure needs to be above
Calculation Groups- Measure Slicer, Measure Header Grouping, Measure to dimension conversion. Complex Table display : https://youtu.be/qMNv67P8Go0
Here is one way. First the model:
Create a new table to use for the columns in the matrix following this pattern:
Custom Matrix = VAR _Dept = ADDCOLUMNS ( VALUES ( 'Department Table'[DEPT.] ), "Index", RANK.EQ ( 'Department Table'[DEPT.], 'Department Table'[DEPT.], ASC ) ) VAR _Rows = DISTINCTCOUNT ( 'Department Table'[DEPT.] ) VAR _Total = { ( "Total", _Rows + 1 ) } VAR _DT = UNION ( _Dept, _Total ) VAR _Metrics = { FORMAT ( DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 ), "MMM" ) & " (LY)", FORMAT ( DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 ), "MMM" ) & " (CY)" } VAR _A = CROSSJOIN ( _DT, _Metrics ) VAR _Diff = { ( "Difference (CY)-(LY)", _Rows + 2, BLANK () ) } RETURN ADDCOLUMNS ( UNION ( _A, _Diff ), "Period", SWITCH ( TRUE (), CONTAINSSTRING ( [Value], "LY" ), 1, CONTAINSSTRING ( [Value], "CY" ), 2, 3 ) )Next the measures, starting with a simple sum measure for the sales:
Sales CY = CALCULATE ( [Sum Sales], FILTER ( ALL ( 'Date Table' ), 'Date Table'[Year] = YEAR ( TODAY () ) && 'Date Table'[MonthNum] = MONTH ( TODAY () ) ) )Sales PY = CALCULATE ( [Sum Sales], FILTER ( ALL ( 'Date Table' ), 'Date Table'[Year] = YEAR ( TODAY () ) - 1 && 'Date Table'[MonthNum] = MONTH ( TODAY () ) ) )and the final measure to use in the matrix:
Measure for Custom Matrix = VAR _CY = CALCULATE([Sales CY], TREATAS(VALUES('Custom Matrix'[DEPT.]), 'Department Table'[DEPT.])) VAR _LY = CALCULATE([Sales PY], TREATAS(VALUES('Custom Matrix'[DEPT.]), 'Department Table'[DEPT.])) VAR _DIFF = [Sales CY] - [Sales PY] VAR _Rows = COUNT('Department Table'[DEPT.]) RETURN SWITCH(TRUE(), AND(MAX('Custom Matrix'[Index]) = _Rows +1, MAX('Custom Matrix'[Period]) = 1), [Sales PY], AND(MAX('Custom Matrix'[Index]) = _Rows +1, MAX('Custom Matrix'[Period]) = 2), [Sales CY], SELECTEDVALUE('Custom Matrix'[Period]) = 1, _LY, SELECTEDVALUE('Custom Matrix'[Period]) = 2, _CY, SELECTEDVALUE('Custom Matrix'[Period]) = 3, _DIFF)Now create the matrix with the customer field as rows, the fields from the Custom Matrix table as columns and the [Measure for Custom Matrix] as values. Turn off the column subtotals and you get:
Sample PBIX file attached
4 Replies
- PaulDBrownCommunity Champion
Here is one way. First the model:
Create a new table to use for the columns in the matrix following this pattern:
Custom Matrix = VAR _Dept = ADDCOLUMNS ( VALUES ( 'Department Table'[DEPT.] ), "Index", RANK.EQ ( 'Department Table'[DEPT.], 'Department Table'[DEPT.], ASC ) ) VAR _Rows = DISTINCTCOUNT ( 'Department Table'[DEPT.] ) VAR _Total = { ( "Total", _Rows + 1 ) } VAR _DT = UNION ( _Dept, _Total ) VAR _Metrics = { FORMAT ( DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 ), "MMM" ) & " (LY)", FORMAT ( DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 ), "MMM" ) & " (CY)" } VAR _A = CROSSJOIN ( _DT, _Metrics ) VAR _Diff = { ( "Difference (CY)-(LY)", _Rows + 2, BLANK () ) } RETURN ADDCOLUMNS ( UNION ( _A, _Diff ), "Period", SWITCH ( TRUE (), CONTAINSSTRING ( [Value], "LY" ), 1, CONTAINSSTRING ( [Value], "CY" ), 2, 3 ) )Next the measures, starting with a simple sum measure for the sales:
Sales CY = CALCULATE ( [Sum Sales], FILTER ( ALL ( 'Date Table' ), 'Date Table'[Year] = YEAR ( TODAY () ) && 'Date Table'[MonthNum] = MONTH ( TODAY () ) ) )Sales PY = CALCULATE ( [Sum Sales], FILTER ( ALL ( 'Date Table' ), 'Date Table'[Year] = YEAR ( TODAY () ) - 1 && 'Date Table'[MonthNum] = MONTH ( TODAY () ) ) )and the final measure to use in the matrix:
Measure for Custom Matrix = VAR _CY = CALCULATE([Sales CY], TREATAS(VALUES('Custom Matrix'[DEPT.]), 'Department Table'[DEPT.])) VAR _LY = CALCULATE([Sales PY], TREATAS(VALUES('Custom Matrix'[DEPT.]), 'Department Table'[DEPT.])) VAR _DIFF = [Sales CY] - [Sales PY] VAR _Rows = COUNT('Department Table'[DEPT.]) RETURN SWITCH(TRUE(), AND(MAX('Custom Matrix'[Index]) = _Rows +1, MAX('Custom Matrix'[Period]) = 1), [Sales PY], AND(MAX('Custom Matrix'[Index]) = _Rows +1, MAX('Custom Matrix'[Period]) = 2), [Sales CY], SELECTEDVALUE('Custom Matrix'[Period]) = 1, _LY, SELECTEDVALUE('Custom Matrix'[Period]) = 2, _CY, SELECTEDVALUE('Custom Matrix'[Period]) = 3, _DIFF)Now create the matrix with the customer field as rows, the fields from the Custom Matrix table as columns and the [Measure for Custom Matrix] as values. Turn off the column subtotals and you get:
Sample PBIX file attached
- OrosPost Prodigy
- amitchandakSuper User
Oros , the Calculation group can help measure needs to be above
Calculation Groups- Measure Slicer, Measure Header Grouping, Measure to dimension conversion. Complex Table display : https://youtu.be/qMNv67P8Go0
- OrosPost Prodigy