Matrix View with new Rows

Hi Anonymous ,

I did the following steps:

• Create a calendar table
  • Related with the data table by date
• Create the following table (no relationships with other tables):

Add the following measures:

Qty calculation = SUM(Data_Table[Value])

CYTD = TOTALYTD([Qty calculation];'Calendar'[Date])

PYTD = TOTALYTD([Qty calculation];DATEADD('Calendar'[Date];-1;YEAR))

Inc/Dec = [CYTD] - [PYTD]

Percent = [Inc/Dec]/[PYTD]

QTY 1 = 
CALCULATE (
    SWITCH (
        TRUE ();
        SELECTEDVALUE ( Measure_Order[ID] ) < 13; CALCULATE (
            [Qty calculation];
            FILTER (
                ALL ( 'Calendar'[Month] );
                'Calendar'[Month] = SELECTEDVALUE ( Measure_Order[ID] )
            )
        ) + 0;
        SELECTEDVALUE ( Measure_Order[ID] ) = 13; [CYTD] + 0;
        SELECTEDVALUE ( Measure_Order[ID] ) = 14; [PYTD] + 0;
        SELECTEDVALUE ( Measure_Order[ID] ) = 15; [Inc/Dec] + 0;
        SELECTEDVALUE ( Measure_Order[ID] ) = 16; [Percent] * 100
    );
    Data_Table[Type] = "QTY1"
) 

QTY 2 = 
CALCULATE (
    SWITCH (
        TRUE ();
        SELECTEDVALUE ( Measure_Order[ID] ) < 13; CALCULATE (
            [Qty calculation];
            FILTER (
                ALL ( 'Calendar'[Month] );
                'Calendar'[Month] = SELECTEDVALUE ( Measure_Order[ID] )
            )
        ) + 0;
        SELECTEDVALUE ( Measure_Order[ID] ) = 13; [CYTD] + 0;
        SELECTEDVALUE ( Measure_Order[ID] ) = 14; [PYTD] + 0;
        SELECTEDVALUE ( Measure_Order[ID] ) = 15; [Inc/Dec] + 0;
        SELECTEDVALUE ( Measure_Order[ID] ) = 16; [Percent] * 100
    );
    Data_Table[Type] = "QTY2"
) 


QTY 3 = 
CALCULATE (
    SWITCH (
        TRUE ();
        SELECTEDVALUE ( Measure_Order[ID] ) < 13; CALCULATE (
            [Qty calculation];
            FILTER (
                ALL ( 'Calendar'[Month] );
                'Calendar'[Month] = SELECTEDVALUE ( Measure_Order[ID] )
            )
        ) + 0;
        SELECTEDVALUE ( Measure_Order[ID] ) = 13; [CYTD] + 0;
        SELECTEDVALUE ( Measure_Order[ID] ) = 14; [PYTD] + 0;
        SELECTEDVALUE ( Measure_Order[ID] ) = 15; [Inc/Dec] + 0;
        SELECTEDVALUE ( Measure_Order[ID] ) = 16; [Percent] * 100
    );
    Data_Table[Type] = "QTY3"
) 


QTY2/QTY1 = DIVIDE([QTY 2] ;[QTY 1];0)

QTY3/QTY1 = DIVIDE([QTY 3] ;[QTY 1];0)

Then create your matrix with the following format:

• Columns:
  • Measure
• Values
  • QTY1
  • QTY2
  • QTY3
  • QTY2/QTY1
  • QTY3/QTY1
• Turn on show values on rows

If you want you can also make the Percent as a percentage however for that you need to create new measures to place in the matrix will be similar to this one:

QTY 1 (formatted) =
CALCULATE (
    SWITCH (
        TRUE ();
        SELECTEDVALUE ( Measure_Order[ID] ) < 16; [QTY 1];
        FORMAT ( [QTY 1] / 100; "###.00%" )
    )
)

You need to have one for each of the QTY be aware that you cannot use these measures for the division within quantities because of the text format.

I added on the measure above so yo could see the result in the attach PBIX file.