cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Develop matrix with colum on right

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
1 ACCEPTED SOLUTION
Community Support

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.

11 REPLIES 11
Community Support

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.

Frequent Visitor

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.

Community Support

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.

Frequent Visitor

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
Frequent Visitor

Community Support

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.

Frequent Visitor

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

Frequent Visitor

I see as following not on right side

Super User

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.

Super User

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

Super User

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors