Forum Discussion
Develop matrix with colum on right
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.
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.
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.
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.
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