How to add variance column to matrix

Hi Anonymous 

You need to re-create the data model.

Open Edit queries, 

1.

create a blank query named "bridge",

enter code in Advanced editor

let
    Source = Table1,
    #"Filtered Rows" = Table.SelectRows(Source, each ([Version] = "Actual"))
in
    #"Filtered Rows"

2. 

create a blank query named "Query1",

enter code in Advanced editor

let
    Source = Table1,
    #"Filtered Rows_budget" = Table.SelectRows(Source, each ([Version] = "Budget")),
    #"Removed Columns_budget" = Table.RemoveColumns(#"Filtered Rows_budget",{"Month"}),
    #"Grouped Rows_budget" = Table.Group(#"Removed Columns_budget", {"Version"}, {{"Sales", each List.Sum([Sales]), type number}, {"COGS", each List.Sum([COGS]), type number}, {"Operating costs", each List.Sum([Operating costs]), type number}}),
    
    #"Filtered Rows_actual" = Table.SelectRows(Source, each ([Version] = "Actual")),
    #"Removed Columns_actual" = Table.RemoveColumns(#"Filtered Rows_actual",{"Month"}),
    #"Grouped Rows_actual" = Table.Group(#"Removed Columns_actual", {"Version"}, {{"Sales", each List.Sum([Sales]), type number}, {"COGS", each List.Sum([COGS]), type number}, {"Operating costs", each List.Sum([Operating costs]), type number}}),

    #"Filtered Rows_prior" = Table.SelectRows(Source, each ([Version] = "Prior Year")),
    #"Merged Queries" = Table.NestedJoin(#"Filtered Rows_prior", {"Month"}, bridge, {"Month"}, "bridge", JoinKind.LeftOuter),
    #"Expanded bridge" = Table.ExpandTableColumn(#"Merged Queries", "bridge", {"Month"}, {"bridge.Month"}),
    #"Filtered Rows_prior1" = Table.SelectRows(#"Expanded bridge", each [bridge.Month] <> null and [bridge.Month] <> ""),
    #"Removed Columns_prior" = Table.RemoveColumns(#"Filtered Rows_prior1",{"Month", "bridge.Month"}),
    #"Grouped Rows_prior" = Table.Group(#"Removed Columns_prior", {"Version"}, {{"Sales", each List.Sum([Sales]), type number}, {"COGS", each List.Sum([COGS]), type number}, {"Operating costs", each List.Sum([Operating costs]), type number}}),
    
    Append_table=Table.Combine({#"Grouped Rows_budget", #"Grouped Rows_actual", #"Grouped Rows_prior"})
in
    Append_table

In this code, i create "budget", "actual", "prior" tables, finally, append all three tables together.

You could open my file to see each table.

3.

In Query1

Select "Sales", "COGS","Operating costs", then select "Unpivot columns",

Next,  select "Attribute" column, pivot column

add two custom columns

Actual vs Budget=([Actual]-[Budget])/[Budget]

Actual vs Prior Year=([Actual]-[Prior Year])/[Prior Year]

Close &&apply

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.