How to add variance column to matrix

[Anonymous]

Hi.  Power BI Gurus. 

Would appreciate your advice on how to create a matrix table like the following:

Year to date May	Actual	Budget	Prior Year	Actual vs Budget	Actual vs Prior Year
Sales	1000	1200	800	-17%	25%
COGS	400	600	300	-33%	33%
Gross margin	600	600	500	0%	20%
Operating costs	500	450	400	11%	25%
Profit before tax	100	150	100	-33%	0%

My dataset captures the P&L data by month and version:

Month	Version	Sales	COGS	Operating costs
Jan	Actual	200	80	100
Feb	Actual	200	80	100
Mar	Actual	200	80	100
Apr	Actual	200	80	100
May	Actual	200	80	100
Jan	Budget	100	50	37.5
Feb	Budget	100	50	37.5
Mar	Budget	100	50	37.5
Apr	Budget	100	50	37.5
May	Budget	100	50	37.5
Jun	Budget	100	50	37.5
Jul	Budget	100	50	37.5
Aug	Budget	100	50	37.5
Sep	Budget	100	50	37.5
Oct	Budget	100	50	37.5
Nov	Budget	100	50	37.5
Dec	Budget	100	50	37.5
Jan	Prior Year	160	60	80
Feb	Prior Year	160	60	80
Mar	Prior Year	160	60	80
Apr	Prior Year	160	60	80
May	Prior Year	160	60	80
Jun	Prior Year	170	70	90
Jul	Prior Year	170	70	90
Aug	Prior Year	170	70	90
Sep	Prior Year	170	70	90
Oct	Prior Year	170	70	90
Nov	Prior Year	170	70	90
Dec	Prior Year	170	70	90

I managed to create a matrix to show the 1st 4 columns (below), but I am stumbled over how to add the variance columns:

	Actual	Budget	Prior Year
Sales	1000	1200	800
COGS	400	600	300
Gross margin	600	600	500
Operating costs	500	450	400
Profit before tax	100	150	100

Pls advise.

[Nars_eci]

Did you find a good solution?

[v-juanli-msft]

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.

[Anonymous]

Hi Maggie.  The solution does not meet my requirement as:

- I need to keep the period & country selection flexible

- there are other charts which use the same dataset

Pls see my sample file in this link:

https://www.dropbox.com/s/v1zv1hycllbeqco/test.pbix?dl=0

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

Create a copy of that query, do the transformation in the copied query.

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.