Forum Discussion
Matrix 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
Thanks for that. Will the months be static (current vs PY) or a dynamic selection?
You'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
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?
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.