Matrix Table

[Oros]

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.

[amitchandak · Accepted solution]

@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

[PaulDBrown · Accepted solution]

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

[PaulDBrown · Accepted solution]

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

[Oros · Accepted solution]

Hello @PaulDBrown ,

Thank you so much!

[amitchandak · Accepted solution]

@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

[Oros]

Hello @amitchandak ,

Thank you so much!