Matrix View with new Rows

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.

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.

Matrix.pbix63 KB

6 Replies

MFelix
Super User
6 years ago
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
  Not applicable
  6 years ago
  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
    Super User
    6 years ago
    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.

Forum Discussion

Matrix View with new Rows

6 Replies

Related content

Viewing/Editing of SQL Views

Understanding Lineage View

Matrix table

TMDL View

Restricted View Policy Bug