Matrix View with new Rows

[Anonymous]

Hi @all,

I have a requirement where any suggestions and logical way will be helpful.

I have a table like below with the data.

I want the matrix visual as below

The Column should have from Jan to Dec and  CYTD, PYTD, Inc/Dec, Percent with all the Rows Qty1, Qty2, Qty3, Qty2/Qty1 and Qty3/Qty1. We will even have filters Region, Brand, Territory. The Matrix should update on selecting any of the filters.

The Values in the columns Jan to Dec will be latest year from table i.e., 2019 data. PYTD is Previous year YTD value.

Your suggestions will be helpfull in building in this Matrix. Thanks in Advance.

[MFelix]

Hi @Anonymous ,

First of all you need to create a calendar table that will allow to have the slicing of the dates and in this case the month columns on your matrix.

Now if you only were considering the Type as you have then you should place the values like this on the matrix:

• Column
  • Month (Calendar Table)
• Rows
  • Type
• Values
  • Value
• Then just go to options and turn on select the show in rows 

Since you want to calculate additional values in this caseqt2/qt1 and qt3/qt1 you need to create a measure for each value of your Type column something similar to this:

QTY1 = CALCULATE(SUM(Table[Value]); Table[Type] = Qty1)

QTY2 = CALCULATE(SUM(Table[Value]); Table[Type] = Qty2)

QTY3 = CALCULATE(SUM(Table[Value]); Table[Type] = Qty3)

QTY2/QTY1 = [QTY2] / [QTY1]

QTY3/QTY1 = [QTY3] / [QTY1]

Now you should make the same steps as previously the only difference is that you should place the measures on the values and rows should not have any values.

For the other columns you need to have some different calculations added but if you can share a sample file would be easier to calculate it.

If you can share a sample in a format to be copy paste or a PBIX file would be helpfull.

Please see this post regarding How to Get Your Question Answered Quickly (courtesy of @Greg_Deckler) and How to provide sample data in the Power BI Forum (courtesy of @ImkeF).

[Anonymous]

Hi @MFelix ,

First of all Thanks for the reply.
I had created measures for Qty1, Qty2 , Qty3, Qty2/Qty1 and Qty3/Qty1. Also placed Types in Rows and Month in Column.

I am facing issue in adding Columns CYTD, PYTD, Inc/Dec and Percent next to Month in Column matrix.

Let me know how to fix that ?

[MFelix]

Can you share some sample data in a format I can copy paste it to PBI?

Those measure need to have some additional calculations and some changes in the way they are calculated.

[Anonymous]

Hi @MFelix ,

Here is the attached sample file. https://docs.google.com/spreadsheets/d/1BuilJnLr7Vf3GZxSFqmlmitpcMcx9J94YN20QQHQW_o/edit#gid=2867526... 

Please let me know if you require anything else.

[MFelix · Accepted solution]

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):

ID	Measure
1	Jan
2	Feb
3	Mar
4	Apr
5	May
6	Jun
7	Jul
8	Aug
9	Sep
10	Oct
11	Nov
12	Dec
13	CYTD
14	PYTD
15	INC/DEC
16	Percent

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.

[Anonymous]

Thanks @MFelix . That was really helpful for my solution. 
You are a Pro. 😀