Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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 |
Solved! Go to 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.
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.
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 @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.
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 |
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.
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
I see as following not on right side
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.
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.)
Hi @ashres11
You can achieve that by hiding columns. It's a tedious work-around but it works.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
87 | |
82 | |
71 | |
49 |
User | Count |
---|---|
143 | |
124 | |
107 | |
60 | |
55 |