Matrix - Multiple Column Header Order

[negi007]

Hi,

I am trying to create a matrix visual but unable to do so. Can someone pl. help

this is what I am able to create

City	2020			2021			2022
	Sales	profit	Revenue	Sales	profit	Revenue	Sales	profit	Revenue
New York	61	66	96	100	100	61	98	95	84
Los Angeles	86	63	73	53	66	53	98	81	80
Chicago	94	95	77	68	63	66	62	83	64
Houston	100	96	57	57	66	65	98	69	73
Phoenix	90	63	84	57	67	58	74	97	50
Philadelphia	54	77	75	71	73	55	64	93	83
San Antonio	70	85	61	95	97	98	80	57	95
San Diego	98	86	70	99	78	54	64	62	63
Dallas	62	69	78	51	97	79	78	56	68

But this is what i want

	Sales	Sales	Sales	profit	profit	profit	Revenue	Revenue	Revenue
City	2020	2021	2022	2020	2021	2022	2020	2021	2022
New York	61	100	98	66	100	95	96	61	84
Los Angeles	86	53	98	63	66	81	73	53	80
Chicago	94	68	62	95	63	83	77	66	64
Houston	100	57	98	96	66	69	57	65	73
Phoenix	90	57	74	63	67	97	84	58	50
Philadelphia	54	71	64	77	73	93	75	55	83
San Antonio	70	95	80	85	97	57	61	98	95
San Diego	98	99	64	86	78	62	70	54	63
Dallas	62	51	78	69	97	56	78	79	68

in the above matrix I am using Sales, Profit and Revenue measures in the 1st column header and second column header is year.  i have provided sample data above as well.

thanks in advance.

[amrans]

Step 1

First of all, your data need to be in following shape. Then you have to add a custom column as mentioned in the snapshot. 

Step 2

Create a custom table like below and also add a custom column as marked below. Set sort order of sections to column "Sqc"

Step 3

Create relationship between two tables by linking custom columns.

Step 4

Create following set of measures.

Current Section = MAX('Sections'[Sections])

Total Sales = SUM(SalesTable[Sales])

Total Profit = SUM(SalesTable[Profit])

Total Revenue = SUM(SalesTable[Revenue])

Total Matrix Value =

Var A=[Current Section]

Var B=SWITCH(A,"Sales",SalesTable[Total Sales],"Profit",SalesTable[Total Profit],"Revenue",[Total Revenue],0)

Return B

Step 5

Use all columns and measures in matrix as shown below. Switch off column sub totals. Then expand to the lowest level on visual header to bring it to following form.

Note:

To make subtotals work, you have to create additional measures. The above example will cover your data arrangement requirements. Hope it satisfies your need. 

[lbendlin]

Please provide sample data in usable (unpivoted) format.

[negi007]

@lbendlin 

below is the data. hope this can help

City	Sales	Year		City	Profit	Year		City	Revenue	Year
New York	61	2020		New York	66	2020		New York	96	2020
New York	100	2021		New York	100	2021		New York	61	2021
New York	98	2022		New York	95	2022		New York	84	2022
Los Angeles	86	2020		Los Angeles	63	2020		Los Angeles	73	2020
Los Angeles	53	2021		Los Angeles	66	2021		Los Angeles	53	2021
Los Angeles	98	2022		Los Angeles	81	2022		Los Angeles	80	2022
Chicago	94	2020		Chicago	95	2020		Chicago	77	2020
Chicago	68	2021		Chicago	63	2021		Chicago	66	2021
Chicago	62	2022		Chicago	83	2022		Chicago	64	2022
Houston	100	2020		Houston	96	2020		Houston	57	2020
Houston	57	2021		Houston	66	2021		Houston	65	2021
Houston	98	2022		Houston	69	2022		Houston	73	2022
Phoenix	90	2020		Phoenix	63	2020		Phoenix	84	2020
Phoenix	57	2021		Phoenix	67	2021		Phoenix	58	2021
Phoenix	74	2022		Phoenix	97	2022		Phoenix	50	2022
Philadelphia	54	2020		Philadelphia	77	2020		Philadelphia	75	2020
Philadelphia	71	2021		Philadelphia	73	2021		Philadelphia	55	2021
Philadelphia	64	2022		Philadelphia	93	2022		Philadelphia	83	2022
San Antonio	70	2020		San Antonio	85	2020		San Antonio	61	2020
San Antonio	95	2021		San Antonio	97	2021		San Antonio	98	2021
San Antonio	80	2022		San Antonio	57	2022		San Antonio	95	2022
San Diego	98	2020		San Diego	86	2020		San Diego	70	2020
San Diego	99	2021		San Diego	78	2021		San Diego	54	2021
San Diego	64	2022		San Diego	62	2022		San Diego	63	2022
Dallas	62	2020		Dallas	69	2020		Dallas	78	2020
Dallas	51	2021		Dallas	97	2021		Dallas	79	2021
Dallas	78	2022		Dallas	56	2022		Dallas	68	2022

[lbendlin]

@negi007   see attached. I leave the sorting up to you as a training exercise.

[negi007]

@lbendlin thank you for your response. in my case problem is that all these are seperate tables and without combining them I wish to have same result. Pl. tell me if that is possible.

[PaulDBrown · Accepted solution]

A couple of solutions, depending on how yo want to depict the columns.

The model is set up as follows:

Dimension tables for City and Year

Create a new table to use as the matrix headers using:

Header =
VAR Metric = { ( "Sales", 1 ), ( "Profit", 2 ), ( "Revenue", 3 ) }
RETURN
    CROSSJOIN ( Metric, 'Year table' )

Set up a relationdhip between the Year dimension table and the Year field in the header table as follows:

1) The simple solution:

Now, based on simple SUM measures por sales, profit and revenue, create the measure to use in the matrix using:

Matrix Value =
SWITCH (
    SELECTEDVALUE ( Header[Order] ),
    1, [Sum Sales],
    2, [Sum Profit],
    3, [Sum Revenue]
)

Set up the matrix using the Year field from the Year Dimension table, the Metric field from the Header Table, the city field from the dimension table and the [Matrix Value] measure to get:

2) the more detailed solution

If you actually want the column headers as you depicted in the example, you need to go further. Create two new calculated columns in the Header table:

Matrix Columns = Header[Metric] & " " & Header[Year]

and this column to sort the Matrix columns by:

Sort MC = Header[Order] * 10000 + Header[Year]

Change the measure for the matrix to:

Matrix Value 1 =
SWITCH (
    SELECTEDVALUE ( Header[Order] ),
    1,
        CALCULATE (
            [Sum Sales],
            TREATAS ( VALUES ( Header[Year] ), 'Year table'[Year] )
        ),
    2,
        CALCULATE (
            [Sum Profit],
            TREATAS ( VALUES ( Header[Year] ), 'Year table'[Year] )
        ),
    3,
        CALCULATE (
            [Sum Revenue],
            TREATAS ( VALUES ( Header[Year] ), 'Year table'[Year] )
        )
)

and finally create the matrix using the Matrix Column field as the columns:

I've attached the sample PBIX file

[negi007]

@PaulDBrown thank you for your detailed response. You have given more than enough ways to tackle this problem.