Forum Discussion
Matrix 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, is it possible to replicate the layout requested by the business?
In my table I have 'Show values in rows' = TRUE and here are my measures:
Quote Count = DISTINCTCOUNT(proposal_primary[proposal_number])
Quote Count PY =
VAR CurrentYearMonthNumber = SELECTEDVALUE ( 'Time'[YearMonthKey] )
VAR PreviousYearMonthNumber = CurrentYearMonthNumber - 12
VAR Result =
CALCULATE (
[Quote Count],
REMOVEFILTERS ( 'Time' ),
'Time'[YearMonthKey] = PreviousYearMonthNumber
)
RETURN
Result
Quote Variance % =
DIVIDE([Quote Count] - [Quote Count PY], [Quote Count PY], 0)
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
10 Replies
any 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?
Hi Paul,
Apologies, let me knock up some test data and I'll share a pbix
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
