Forum Discussion
Matrix Table
- 3 years ago
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
- 3 years ago
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
- 3 years ago
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