Total Average and Yearly Percent change on Matrix visual where values have been switched to rows.

[JC_Silva]

Hello,

I need help calculating a X year average and the yearly percent change in a Matrix visual where the values have been switched to rows.

As an example, here is the data that I am using:

When I import it to Power BI, I use the Matrix visual to pivot the GrossSales, NetSales, and Inventory columns so they are shown as rows with years running accross the top:

However, the request from the user is to have the three and five year average as additional columns, in addition to yearly percent changes as rows, so it would look like this:

Im stuck trying to write a measure that would do either of these. Any guidance would be greatly appreciated.

[v-yanjiang-msft · Accepted solution]

Hi @JC_Silva ,

According to your description, if you want to display the result exactly like yours, you should create tables contain all elements both for Axis and Column in the matrix. Here's my solution.

1.Unpivot the GrossSales, NetSales, and Inventory columns as you described, get the following table.

2.Create two new tables.

Category:

Row:

Make relationship like this:

3.Create a measure. This formula is a bit long because many values ​​need to be defined separately. It can be roughly divided into three parts, original data, percentage and average.

Measure =
IF (
    NOT ( MAX ( 'Row'[Row] ) IN { "3 Year Average", "5 Year Average" } )
        && MAX ( 'Category'[Category] ) IN VALUES ( 'Table'[Category] ),
    SUM ( 'Table'[Value] ),
    IF (
        MAX ( 'Category'[Category] ) = "GrossSalesYearPercentChange"
            && NOT ( MAX ( 'Row'[Row] ) IN { "3 Year Average", "5 Year Average" } ),
        IF (
            MAX ( 'Row'[Row] ) = MINX ( ALL ( 'Row' ), 'Row'[Row] ),
            BLANK (),
            DIVIDE (
                SUMX (
                    FILTER (
                        ALL ( 'Table' ),
                        'Table'[Year] = VALUE ( MAX ( 'Row'[Row] ) )
                            && 'Table'[Category] = "Gross Sales"
                    ),
                    'Table'[Value]
                )
                    - SUMX (
                        FILTER (
                            ALL ( 'Table' ),
                            'Table'[Year]
                                = VALUE ( MAX ( 'Row'[Row] ) ) - 1
                                && 'Table'[Category] = "Gross Sales"
                        ),
                        'Table'[Value]
                    ),
                SUMX (
                    FILTER (
                        ALL ( 'Table' ),
                        'Table'[Year] = VALUE ( MAX ( 'Row'[Row] ) )
                            && 'Table'[Category] = "Gross Sales"
                    ),
                    'Table'[Value]
                )
            )
        ),
        IF (
            MAX ( 'Category'[Category] ) = "NetSalesYearPercentChange"
                && NOT ( MAX ( 'Row'[Row] ) IN { "3 Year Average", "5 Year Average" } ),
            IF (
                MAX ( 'Row'[Row] ) = MINX ( ALL ( 'Row' ), 'Row'[Row] ),
                BLANK (),
                DIVIDE (
                    SUMX (
                        FILTER (
                            ALL ( 'Table' ),
                            'Table'[Year] = VALUE ( MAX ( 'Row'[Row] ) )
                                && 'Table'[Category] = "Net Sales"
                        ),
                        'Table'[Value]
                    )
                        - SUMX (
                            FILTER (
                                ALL ( 'Table' ),
                                'Table'[Year]
                                    = VALUE ( MAX ( 'Row'[Row] ) ) - 1
                                    && 'Table'[Category] = "Net Sales"
                            ),
                            'Table'[Value]
                        ),
                    SUMX (
                        FILTER (
                            ALL ( 'Table' ),
                            'Table'[Year] = VALUE ( MAX ( 'Row'[Row] ) )
                                && 'Table'[Category] = "Net Sales"
                        ),
                        'Table'[Value]
                    )
                )
            ),
            IF (
                MAX ( 'Category'[Category] ) = "InventoryYearPercentChange"
                    && NOT ( MAX ( 'Row'[Row] ) IN { "3 Year Average", "5 Year Average" } ),
                IF (
                    MAX ( 'Row'[Row] ) = MINX ( ALL ( 'Row' ), 'Row'[Row] ),
                    BLANK (),
                    DIVIDE (
                        SUMX (
                            FILTER (
                                ALL ( 'Table' ),
                                'Table'[Year] = VALUE ( MAX ( 'Row'[Row] ) )
                                    && 'Table'[Category] = "Inventory"
                            ),
                            'Table'[Value]
                        )
                            - SUMX (
                                FILTER (
                                    ALL ( 'Table' ),
                                    'Table'[Year]
                                        = VALUE ( MAX ( 'Row'[Row] ) ) - 1
                                        && 'Table'[Category] = "Inventory"
                                ),
                                'Table'[Value]
                            ),
                        SUMX (
                            FILTER (
                                ALL ( 'Table' ),
                                'Table'[Year] = VALUE ( MAX ( 'Row'[Row] ) )
                                    && 'Table'[Category] = "Inventory"
                            ),
                            'Table'[Value]
                        )
                    )
                ),
                IF (
                    MAX ( 'Row'[Row] ) = "3 Year Average",
                    SUMX (
                        FILTER (
                            ALL ( 'Table' ),
                            'Table'[Category] = MAX ( 'Category'[Category] )
                                && 'Table'[Year]
                                    IN {
                                        MAXX ( ALL ( 'Table' ), 'Table'[Year] ),
                                        MAXX ( ALL ( 'Table' ), 'Table'[Year] ) - 1,
                                        MAXX ( ALL ( 'Table' ), 'Table'[Year] ) - 2
                                    }
                        ),
                        'Table'[Value]
                    ) / 3,
                    IF (
                        MAX ( 'Row'[Row] ) = "5 Year Average",
                        SUMX (
                            FILTER (
                                ALL ( 'Table' ),
                                'Table'[Category] = MAX ( 'Category'[Category] )
                                    && 'Table'[Year]
                                        IN {
                                            MAXX ( ALL ( 'Table' ), 'Table'[Year] ),
                                            MAXX ( ALL ( 'Table' ), 'Table'[Year] ) - 1,
                                            MAXX ( ALL ( 'Table' ), 'Table'[Year] ) - 2,
                                            MAXX ( ALL ( 'Table' ), 'Table'[Year] ) - 3,
                                            MAXX ( ALL ( 'Table' ), 'Table'[Year] ) - 4,
                                            MAXX ( ALL ( 'Table' ), 'Table'[Year] ) - 5
                                        }
                            ),
                            'Table'[Value]
                        ) / 5
                    )
                )
            )
        )
    )
)

Get the expected 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.

[JC_Silva]

Thank you! This worked great!

[Ashish_Mathur]

Hi,

I can help you with calculating yearly percentage change (not the 3 and 5 year average).  If you are OK with that, then share the link of the PBI file.