Forum Discussion
Matrix Layout
- 3 years ago
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
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?
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:
- JimJim3 years agoResponsive Resident
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.