Forum Discussion
Responsive ResidentMatrix Layout
See if this works for you.
First, create a new table with the structure you need for the matrix columns:
Matrix Columns = VAR _periods = SUMMARIZE ( 'time', 'time'[Month], 'time'[YearMonth] ) VAR _Other = { ( "Same Month PY", 1000000 ), ( "Var", 2000000 ) } RETURN UNION ( _periods, _Other )Leave this table unrelated in the model.
Next create two measures (one for Count and the other for Value) following this pattern:
Quote Count Summary = VAR _SelPeriod = SELECTEDVALUE ( 'time'[YearMonth] ) VAR _PY = [Quote Count PY] VAR _VR = FORMAT ( [Quote Count Variance (%)], "percent" ) RETURN SWITCH ( SELECTEDVALUE ( 'Matrix Columns'[YearMonth] ), _SelPeriod, [Quote Count], 1000000, _PY, 2000000, _VR )Next create the matrix using the 'Matrix Columns[Month]' as the columns, add both measures and format to be shown on rows, add some conditional formatting and a dynamic title and you will get this:
I've attached the sample PBIX file
Responsive ResidentYou're welcome
There will be a slicer at the top allowing users to select a month, comparison will always be between selected month and the same month for the previous year. Granularity will never go below month
Community ChampionSee if this works for you.
First, create a new table with the structure you need for the matrix columns:
Matrix Columns =
VAR _periods =
SUMMARIZE ( 'time', 'time'[Month], 'time'[YearMonth] )
VAR _Other = { ( "Same Month PY", 1000000 ), ( "Var", 2000000 ) }
RETURN
UNION ( _periods, _Other )
Leave this table unrelated in the model.
Next create two measures (one for Count and the other for Value) following this pattern:
Quote Count Summary =
VAR _SelPeriod =
SELECTEDVALUE ( 'time'[YearMonth] )
VAR _PY = [Quote Count PY]
VAR _VR =
FORMAT ( [Quote Count Variance (%)], "percent" )
RETURN
SWITCH (
SELECTEDVALUE ( 'Matrix Columns'[YearMonth] ),
_SelPeriod, [Quote Count],
1000000, _PY,
2000000, _VR
)
Next create the matrix using the 'Matrix Columns[Month]' as the columns, add both measures and format to be shown on rows, add some conditional formatting and a dynamic title and you will get this:
I've attached the sample PBIX file
- JimJim
PaulDBrown , thank you so much, this is amazing. I won't pretend I understand completely what is going on but I will take some time to work it out.
I have several other measures I need to add to the matrix, is it easy to add to it?
- PaulDBrown
Sure. Just create the measures following the pattern of the measure shown. (BTW I edited the conditional formatting measures. I uploaded a new file, but It may have been after you looked at the post, so download the new file just in case).
Let us know if you have any further issues. - PaulDBrown
Btw, I'd be happy to work through the process if that helps. I wrote a blog post about custom matrix layouts which might also help:
- JimJim
I really appreciate the offer, I spent some time with your pbix and have a much better understanding of what you have done. I would say the only thing I am not 100% on is the SWITCH(SELECTEDVALUE part but leave it with me 😊
Thank you for sharing your blog post, I have bookmarked it and will definitely read it once I find some time.