Questions about Matrix, put custom values and pertentage into a Matrix Visual

Question

Hi, everyone,&nbsp;Recently I was assigned a task to create a table looking like the screenshot below.&nbsp;&nbsp;And the source data looks like this:This could be easily achieved with the table visual. However, my boss wants to see a rolling 12 months table. From the next month, the first column would be "Prior to Aug", and the second column would be "Aug".&nbsp;&nbsp;According to the link below, I might be able to do it in a matrix. However, the "Total" Column does not equal to the column total, it is the total number of orders later or after July.https://community.powerbi.com/t5/Desktop/Dynamic-Column-Header-Names-based-month/td-p/1847178&nbsp;&nbsp;In addition, I don't know how to put the percentage field onto the matrix, which equals to the number of orders later or after July divided by the number of all orders.&nbsp;amitchandak&nbsp;,&nbsp;SpartaBI&nbsp;,&nbsp;Ashish_Mathur&nbsp;,&nbsp;DataInsights&nbsp;,&nbsp;MFelix&nbsp;

v-yanjiang-msft · Accepted Answer

Hi&nbsp;Jacob_Li&nbsp;,
According to your description, here's my solution.
1.Create a new table.
Table =
UNION (
    ROW (
        "Month",
            "Prior to "
                &amp; FORMAT ( DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) + 1, 1 ), "MMM" )
    ),
    ROW (
        "Month", FORMAT ( DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) + 1, 1 ), "MMM" )
    ),
    ROW (
        "Month", FORMAT ( DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) + 2, 1 ), "MMM" )
    ),
    ROW (
        "Month", FORMAT ( DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) + 3, 1 ), "MMM" )
    ),
    ROW (
        "Month", FORMAT ( DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) + 4, 1 ), "MMM" )
    ),
    ROW (
        "Month", FORMAT ( DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) + 5, 1 ), "MMM" )
    ),
    ROW ( "Month", "Total" ),
    ROW ( "Month", "Order%" )
)
In order to let the Month column sort as expected, create a calculated column in the new table.
Column =
SWITCH (
    'Table'[Month],
    "Prior to "
        &amp; FORMAT ( DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) + 1, 1 ), "MMM" ), 1,
    FORMAT ( DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) + 1, 1 ), "MMM" ), 2,
    FORMAT ( DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) + 2, 1 ), "MMM" ), 3,
    FORMAT ( DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) + 3, 1 ), "MMM" ), 4,
    FORMAT ( DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) + 4, 1 ), "MMM" ), 5,
    FORMAT ( DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) + 5, 1 ), "MMM" ), 6,
    "Total", 7,
    "Order%", 8
)

Result:

2.Create a measure.
Value =
SWITCH (
    MAX ( 'Table'[Column] ),
    1,
        COUNTROWS (
            FILTER (
                'Table (2)',
                'Table (2)'[Customer] = MAX ( 'Table (2)'[Customer] )
                    &amp;&amp; MONTH ( 'Table (2)'[Order Date] ) &lt;= MONTH ( TODAY () )
            )
        ) + 0,
    2,
        COUNTROWS (
            FILTER (
                'Table (2)',
                'Table (2)'[Customer] = MAX ( 'Table (2)'[Customer] )
                    &amp;&amp; MONTH ( 'Table (2)'[Order Date] )
                        = MONTH ( TODAY () ) + 1
            )
        ) + 0,
    3,
        COUNTROWS (
            FILTER (
                'Table (2)',
                'Table (2)'[Customer] = MAX ( 'Table (2)'[Customer] )
                    &amp;&amp; MONTH ( 'Table (2)'[Order Date] )
                        = MONTH ( TODAY () ) + 2
            )
        ) + 0,
    4,
        COUNTROWS (
            FILTER (
                'Table (2)',
                'Table (2)'[Customer] = MAX ( 'Table (2)'[Customer] )
                    &amp;&amp; MONTH ( 'Table (2)'[Order Date] )
                        = MONTH ( TODAY () ) + 3
            )
        ) + 0,
    5,
        COUNTROWS (
            FILTER (
                'Table (2)',
                'Table (2)'[Customer] = MAX ( 'Table (2)'[Customer] )
                    &amp;&amp; MONTH ( 'Table (2)'[Order Date] )
                        = MONTH ( TODAY () ) + 4
            )
        ) + 0,
    6,
        COUNTROWS (
            FILTER (
                'Table (2)',
                'Table (2)'[Customer] = MAX ( 'Table (2)'[Customer] )
                    &amp;&amp; MONTH ( 'Table (2)'[Order Date] )
                        = MONTH ( TODAY () ) + 5
            )
        ) + 0,
    7,
        COUNTROWS (
            FILTER (
                'Table (2)',
                'Table (2)'[Customer] = MAX ( 'Table (2)'[Customer] )
                    &amp;&amp; MONTH ( 'Table (2)'[Order Date] ) &gt; MONTH ( TODAY () )
                    &amp;&amp; MONTH ( 'Table (2)'[Order Date] )
                        &lt; MONTH ( TODAY () ) + 6
            )
        ) + 0,
    8,
        FORMAT (
            DIVIDE (
                COUNTROWS (
                    FILTER (
                        'Table (2)',
                        'Table (2)'[Customer] = MAX ( 'Table (2)'[Customer] )
                            &amp;&amp; MONTH ( 'Table (2)'[Order Date] ) &gt; MONTH ( TODAY () )
                            &amp;&amp; MONTH ( 'Table (2)'[Order Date] )
                                &lt; MONTH ( TODAY () ) + 6
                    )
                ),
                COUNTROWS (
                    FILTER ( 'Table (2)', 'Table (2)'[Customer] = MAX ( 'Table (2)'[Customer] ) )
                )
            ),
            "Percent"
        )
)

Put Customer in Rows, rank column and Month in Columns, Value measure in Values, get the result.

I attach my sample below for reference.
&nbsp;
Best Regards,Community Support Team _ kalyj
If this post&nbsp;helps,&nbsp;then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly.
&nbsp;

Forum Discussion

Questions about Matrix, put custom values and pertentage into a Matrix Visual

2 Replies

Related content

Matrix component - order question

Bookmark questions

Architecture question and brainstorming

Field Parameter Question

Unpivot Question