Dynamic Formula based on columns selection in report

Hi Anonymous,

 

Please see below steps:

 

Unpivot table to convert table to below structure.

 

Duplicate column [Month:Category], then, split it by ":".

 

Save and apply all above changes. Return back to data view, create a calculated column:

Difference =
CALCULATE (
    SUM ( 'Month Data'[Value] ),
    FILTER (
        ALLEXCEPT ( 'Month Data', 'Month Data'[Month] ),
        'Month Data'[Forecast/Actual] = "Actual"
    )
)
    - CALCULATE (
        SUM ( 'Month Data'[Value] ),
        FILTER (
            ALLEXCEPT ( 'Month Data', 'Month Data'[Month] ),
            'Month Data'[Forecast/Actual] = "Forecast"
        )
    )

 

Based on above table, create a new calculated table:

Month Data2 =
UNION (
    SELECTCOLUMNS (
        'Month Data',
        "Month:Category", 'Month Data'[Month:Category],
        "Values", 'Month Data'[Value],
        "Month", 'Month Data'[Month],
        "Forecast/Actual", 'Month Data'[Forecast/Actual]
    ),
    SELECTCOLUMNS (
        'Month Data',
        "Month:Category", "Difference",
        "Values", 'Month Data'[Difference],
        "Month", 'Month Data'[Month],
        "Forecast/Actual", "Difference"
    )
)

 

Insert a Matrix visual, drag corresponding fields from 'Month Data2' into it.

 

Best regards,

Yuliana Gu