Calculated Row in Table or Matrix Table with Value of the raw Headers extending to December

[whillerz]

Hi,

Good Morning. I need help.  My raw table is like in the screenshot below and need help on creating a calculated row that extends - in the case of the screenshot, can be January to December.

- Highlighted in Yellow is the calculated Row

- There will be filters/slicers in the report that can be Group 1 to Group 3

- *can have multiple calculated Rows (different metrics/calculation)

- Target look of the Table/Matrix is a summary of the Raw

              - In the case of the screenshot, Columns Category to March will be displayed.

-and probably custom sort the Category

raw data look:

[Anonymous · Accepted solution]

Hi  @whillerz ,

Here are the steps you can follow：

1. Create calculated table.

Table 2 =
VAR _table =
    SUMMARIZE (
        'Table',
        'Table'[Group1],
        'Table'[Group2],
        'Table'[Group3],
        "Category", "Offered/Forecast",
        "January",
            FORMAT (
                DIVIDE (
                    SUMX (
                        FILTER (
                            ALL ( 'Table' ),
                            'Table'[Group1] = EARLIER ( 'Table'[Group1] )
                                && 'Table'[Group2] = EARLIER ( 'Table'[Group2] )
                                && 'Table'[Group3] = EARLIER ( 'Table'[Group3] )
                                && 'Table'[Category] = "Offered"
                        ),
                        [January]
                    ),
                    SUMX (
                        FILTER (
                            ALL ( 'Table' ),
                            'Table'[Group1] = EARLIER ( 'Table'[Group1] )
                                && 'Table'[Group2] = EARLIER ( 'Table'[Group2] )
                                && 'Table'[Group3] = EARLIER ( 'Table'[Group3] )
                                && 'Table'[Category] = "Forecast"
                        ),
                        [January]
                    )
                ),
                "Percent"
            ),
        "February",
            FORMAT (
                DIVIDE (
                    SUMX (
                        FILTER (
                            ALL ( 'Table' ),
                            'Table'[Group1] = EARLIER ( 'Table'[Group1] )
                                && 'Table'[Group2] = EARLIER ( 'Table'[Group2] )
                                && 'Table'[Group3] = EARLIER ( 'Table'[Group3] )
                                && 'Table'[Category] = "Offered"
                        ),
                        [February]
                    ),
                    SUMX (
                        FILTER (
                            ALL ( 'Table' ),
                            'Table'[Group1] = EARLIER ( 'Table'[Group1] )
                                && 'Table'[Group2] = EARLIER ( 'Table'[Group2] )
                                && 'Table'[Group3] = EARLIER ( 'Table'[Group3] )
                                && 'Table'[Category] = "Forecast"
                        ),
                        [February]
                    )
                ),
                "Percent"
            ),
        "March",
            FORMAT (
                DIVIDE (
                    SUMX (
                        FILTER (
                            ALL ( 'Table' ),
                            'Table'[Group1] = EARLIER ( 'Table'[Group1] )
                                && 'Table'[Group2] = EARLIER ( 'Table'[Group2] )
                                && 'Table'[Group3] = EARLIER ( 'Table'[Group3] )
                                && 'Table'[Category] = "Offered"
                        ),
                        [March]
                    ),
                    SUMX (
                        FILTER (
                            ALL ( 'Table' ),
                            'Table'[Group1] = EARLIER ( 'Table'[Group1] )
                                && 'Table'[Group2] = EARLIER ( 'Table'[Group2] )
                                && 'Table'[Group3] = EARLIER ( 'Table'[Group3] )
                                && 'Table'[Category] = "Forecast"
                        ),
                        [March]
                    )
                ),
                "Percent"
            )
    )
RETURN
    UNION ( 'Table', _table )

2. Result:

According to Power BI's design philosophy, there can't be different data types in the same column, so here they all become text, or you can use Format(), which all become decimal data types

Best Regards,

Liu Yang

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

[whillerz]

Thank you very much! This is perfect!