Forum Discussion
JimJim
Responsive Resident
4 years agoMatrix Layout
Hi Guys, The business have asked me to build a table comparing months in the following format: I have the measures in place but my table looks like this: Using my current measures, ...
- 4 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
Community Champion
4 years agoany chance you can share some dummy data?
Also...I take it you wish to compare two specific months, correct? If so, how will the user select the months to be depicted?
- JimJim4 years ago
Responsive Resident
Hi Paul,
Apologies, let me knock up some test data and I'll share a pbix