Forum Discussion

Oros's avatar
Oros
Post Prodigy
3 years ago
Solved

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

DATECUSTOMERSALESDEPT.
Nov. 1, 2021ABC25A
Nov. 2, 2021XZY50B
Dec. 5, 2022ABC35A

 

Using Matrix table,  this is the result that I am getting

 

CUSTOMERDEPT. ADEPT. ADEPT. BDEPT. BTOTAL (LY)TOTAL (CY)
 NOV. (LY)NOV. (CY)NOV. (LY)NOV. (CY)  
CUST. ABC200100500300700400

 

 

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.

CUSTOMERDEPT.  ADEPT. BTOTAL (LY)DEPT. ADEPT. BTOTAL (CY)DIFFERENCE (CY)-(LY)
 (LY) NOV(LY) NOV (CY) NOV(CY) NOV  
CUST. ABC200

500

700100300400-300

 

Here is the table and matrix illustration:

 

 

 

Any help is highly appreciated.  Thanks.

 

 

 

 

  • 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

  • PaulDBrown's avatar
    PaulDBrown
    Community 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

     

  • 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