Matrix Layout

[JimJim]

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)

[JimJim]

@PaulDBrown  link to pbix with test data:

https://1drv.ms/u/s!ArazqhFmvkSJlmVxJRRpSR1dAG4m?e=m0jo3l

[PaulDBrown]

Thanks for that. Will the months be static (current vs PY) or a dynamic selection?

[JimJim]

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

[PaulDBrown · Accepted solution]

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

[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]

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:

Creating a custom matrix layout 

[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.

[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]

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?

[JimJim]

Hi Paul, 

Apologies, let me knock up some test data and I'll share a pbix