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

[Jacob_Li]

Hi, everyone,

Recently I was assigned a task to create a table looking like the screenshot below.

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

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 

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.

@amitchandak , @SpartaBI , @Ashish_Mathur , @DataInsights , @MFelix 

[v-yanjiang-msft · Accepted solution]

Hi @Jacob_Li ,

According to your description, here's my solution.

1.Create a new table.

Table =
UNION (
    ROW (
        "Month",
            "Prior to "
                & 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 "
        & 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] )
                    && MONTH ( 'Table (2)'[Order Date] ) <= MONTH ( TODAY () )
            )
        ) + 0,
    2,
        COUNTROWS (
            FILTER (
                'Table (2)',
                'Table (2)'[Customer] = MAX ( 'Table (2)'[Customer] )
                    && MONTH ( 'Table (2)'[Order Date] )
                        = MONTH ( TODAY () ) + 1
            )
        ) + 0,
    3,
        COUNTROWS (
            FILTER (
                'Table (2)',
                'Table (2)'[Customer] = MAX ( 'Table (2)'[Customer] )
                    && MONTH ( 'Table (2)'[Order Date] )
                        = MONTH ( TODAY () ) + 2
            )
        ) + 0,
    4,
        COUNTROWS (
            FILTER (
                'Table (2)',
                'Table (2)'[Customer] = MAX ( 'Table (2)'[Customer] )
                    && MONTH ( 'Table (2)'[Order Date] )
                        = MONTH ( TODAY () ) + 3
            )
        ) + 0,
    5,
        COUNTROWS (
            FILTER (
                'Table (2)',
                'Table (2)'[Customer] = MAX ( 'Table (2)'[Customer] )
                    && MONTH ( 'Table (2)'[Order Date] )
                        = MONTH ( TODAY () ) + 4
            )
        ) + 0,
    6,
        COUNTROWS (
            FILTER (
                'Table (2)',
                'Table (2)'[Customer] = MAX ( 'Table (2)'[Customer] )
                    && MONTH ( 'Table (2)'[Order Date] )
                        = MONTH ( TODAY () ) + 5
            )
        ) + 0,
    7,
        COUNTROWS (
            FILTER (
                'Table (2)',
                'Table (2)'[Customer] = MAX ( 'Table (2)'[Customer] )
                    && MONTH ( 'Table (2)'[Order Date] ) > MONTH ( TODAY () )
                    && MONTH ( 'Table (2)'[Order Date] )
                        < MONTH ( TODAY () ) + 6
            )
        ) + 0,
    8,
        FORMAT (
            DIVIDE (
                COUNTROWS (
                    FILTER (
                        'Table (2)',
                        'Table (2)'[Customer] = MAX ( 'Table (2)'[Customer] )
                            && MONTH ( 'Table (2)'[Order Date] ) > MONTH ( TODAY () )
                            && MONTH ( 'Table (2)'[Order Date] )
                                < 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.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Jacob_Li , I doubt you can add separate column at the end. You can replace % column using isfiltered or isinscope

You also check for Hybrid table work around

if you are looking for a Hybrid display with Matrix Column and measure
https://community.powerbi.com/t5/Community-Blog/Creating-a-custom-or-hybrid-matrix-in-PowerBI/ba-p/1...
https://community.powerbi.com/t5/Quick-Measures-Gallery/The-New-Hotness-Custom-Matrix-Hierarchy/m-p/...

vote for Hybrid Table
https://ideas.powerbi.com/ideas/idea/?ideaid=9bc32b23-1eb1-4e74-8b34-349887b37ebc