Develop matrix with colum on right

[ashres11]

I need to develop matrix table with rightside column. something like following.

Column1	Column 2	Column3	Matrix1	Matrix2	Matrix 3	Matrix 4	Column 4	Column 5	Column 6
City	State	Building	01/01	01/02	01/03	01/04	min	max	avg
santa monica	California	XYZ St	10	22	7	15	7	22	14

[v-xinruzhu-msft]

Hi @ashres11 

You can try the following solution.

Sample data 

Sample measure

Sample = SUM('Table'[Value])

1.Create a type table

2.Create a measure

Measure2 =
VAR a =
    MAXX (
        FILTER ( ALLSELECTED ( 'Table' ), [Cat] IN VALUES ( 'Table'[Cat] ) ),
        [Sample]
    )
VAR b =
    MINX (
        FILTER ( ALLSELECTED ( 'Table' ), [Cat] IN VALUES ( 'Table'[Cat] ) ),
        [Sample]
    )
VAR c =
    AVERAGEX (
        FILTER ( ALLSELECTED ( 'Table' ), [Cat] IN VALUES ( 'Table'[Cat] ) ),
        [Sample]
    )
RETURN
    SWITCH (
        SELECTEDVALUE ( 'Type'[Type] ),
        "Avg", c,
        "Max", a,
        "Min", b,
        CALCULATE ( [Sample], FORMAT ( 'Table'[Date], "" ) IN VALUES ( 'Type'[Type] ) )
    )

Then put the following field to the matrix.

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ashres11]

it works now however , I am not able to use my slicer - Jan, 24, feb 24 etc... still it shows data for all months. I have created new column mmm-yy formate but that filter is not applying on this report. rest of other slicer filter works.

[v-xinruzhu-msft]

Hi @ashres11 

Please refer to the following.

1.Create a calendar table.

Calendar = ADDCOLUMNS(CALENDAR(DATE(2023,1,1),DATE(2023,12,31)),"Year-Month",FORMAT([Date],"YYYY-MM"))

2.Change the type table to the following.

Type = var a=SUMMARIZE('Calendar',[Year-Month])
var b={"Min","Max","Avg"}
return UNION(a,b)

3.Create a relationship between calendar table and data table.

4.Change the measure to the following.

Measure2 =
VAR a =
    MAXX (
        FILTER ( ALLSELECTED ( 'Table' ), [Cat] IN VALUES ( 'Table'[Cat] ) ),
        [Sample]
    )
VAR b =
    MINX (
        FILTER ( ALLSELECTED ( 'Table' ), [Cat] IN VALUES ( 'Table'[Cat] ) ),
        [Sample]
    )
VAR c =
    AVERAGEX (
        FILTER ( ALLSELECTED ( 'Table' ), [Cat] IN VALUES ( 'Table'[Cat] ) ),
        [Sample]
    )
RETURN
    SWITCH (
        SELECTEDVALUE ( 'Type'[Year-Month] ),
        "Avg", c,
        "Max", a,
        "Min", b,
        CALCULATE (
            [Sample],
            FILTER ( 'Calendar', 'Calendar'[Year-Month] IN VALUES ( 'Type'[Year-Month] ) )
        )
    )

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ashres11]

Thanks for your support.  let say  if I have filter on top where select month such as January 2024 or any other months (iwas able to fix this)

Also tomorrow when main table is updated with April, may data, will this dates are dynamically calculated.... or is type table kind of static.

Also Type table, date is converted to text so now visual matrix  table is not showing chronological order for date.

IN bottom Table should look like

drop down menu somewhere in report and then select January 2024

January 2024

February 2024

Once I select January 2024 then it tshould be matrix table with date

City	Building	01/01/2024	01/02/2024	01/03/2024	Min	Max	Avg
a	b	5	7	9	5	9	7
d	e	11	12	25	11	25	16

[ashres11]

[v-xinruzhu-msft · Accepted solution]

Hi @ashres11 

Based on your description, you just need to update the calendar table to the following.

Calendar = ADDCOLUMNS(CALENDAR(MIN('Table'[Date]),MAX('Table'[Date])),"Year-Month",FORMAT([Date],"YYYY-MM"))

2.Change the type table to the following.

Type = var a=SUMMARIZE('Table',[Date])
var b={"Min","Max","Avg"}
return UNION(a,b)

3.Change the measure to the following.

Measure2 =
VAR a =
    MAXX (
        FILTER ( ALLSELECTED ( 'Table' ), [Cat] IN VALUES ( 'Table'[Cat] ) ),
        [Sample]
    )
VAR b =
    MINX (
        FILTER ( ALLSELECTED ( 'Table' ), [Cat] IN VALUES ( 'Table'[Cat] ) ),
        [Sample]
    )
VAR c =
    AVERAGEX (
        FILTER ( ALLSELECTED ( 'Table' ), [Cat] IN VALUES ( 'Table'[Cat] ) ),
        [Sample]
    )
RETURN
    SWITCH (
        SELECTEDVALUE ( 'Type'[Type] ),
        "Avg", c,
        "Max", a,
        "Min", b,
        CALCULATE (
            [Sample],
            FILTER ( 'Calendar', FORMAT ( [Date], "" ) IN VALUES ( 'Type'[Type] ) )
        )
    )

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ashres11]

last piece is how to can be setup ascending order or date and text in specific order non-alphabetical order for Min, max and Avg

[ashres11]

I see as following not on right side

[lbendlin]

Don't try to make Power BI into something it is not.  Use separate visuals.

@gmsamborn was very generous to describe this as tedious. I would have used different wording.

[gmsamborn]

I would try to shrink the column you don't want to see.  For example, if it's [Max1] that you want to hide then you'll have to shrink it below each of the dates.

Does that make sense?

(I said at the start that it is tedious.)

[gmsamborn]

Hi @ashres11 

You can achieve that by hiding columns.  It's a tedious work-around but it works.

Custom Matrix Columns.pbix

• For both Values and Column headers, set Text-Wrap to off.
• Add the measures in the order you want the totals to appear.
• Shrink the Min, Max, and Avg columns (in the Month columns) until they are invisible.
• In the column totals, shrink Total.
• In the column totals, change the Subtotal Label to a space to hide.