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
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
- Oros3 years ago
Post Prodigy