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 ...
  • amitchandak's avatar
    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

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

     

  • Oros's avatar
    Oros
    3 years ago

    Hello PaulDBrown ,

     

    Thank you so much!