Forum Discussion

JimJim's avatar
JimJim
Icon for Responsive Resident rankResponsive Resident
4 years ago
Solved

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, ...
Need Help
  • PaulDBrown's avatar
    PaulDBrown
    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's avatar
    PaulDBrown
    Icon for Community Champion rankCommunity Champion
    4 years ago

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

    • JimJim's avatar
      JimJim
      Icon for Responsive Resident rankResponsive Resident
      4 years ago

      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's avatar
        PaulDBrown
        Icon for Community Champion rankCommunity Champion
        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