Forum Discussion
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 |
Hi Anonymous
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
Hi Anonymous
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.
Hi Anonymous
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.
- gmsamborn
Super User
Hi Anonymous
You can achieve that by hiding columns. It's a tedious work-around but it works.
- 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.
- 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.)
