Hi. Power BI Gurus. Would appreciate your advice on how to create a matrix table like the following:Year to date MayActualBudgetPrior YearActual vs BudgetActual vs Prior YearSales10001200800-17%25%COGS400600300-33%33%Gross margin6006005000%20%Operating costs50045040011%25%Profit before tax100150100-33%0% My dataset captures the P&L data by month and version:MonthVersionSalesCOGSOperating costsJanActual20080100FebActual20080100MarActual20080100AprActual20080100MayActual20080100JanBudget1005037.5FebBudget1005037.5MarBudget1005037.5AprBudget1005037.5MayBudget1005037.5JunBudget1005037.5JulBudget1005037.5AugBudget1005037.5SepBudget1005037.5OctBudget1005037.5NovBudget1005037.5DecBudget1005037.5JanPrior Year1606080FebPrior Year1606080MarPrior Year1606080AprPrior Year1606080MayPrior Year1606080JunPrior Year1707090JulPrior Year1707090AugPrior Year1707090SepPrior Year1707090OctPrior Year1707090NovPrior Year1707090DecPrior Year1707090 I managed to create a matrix to show the 1st 4 columns (below), but I am stumbled over how to add the variance columns: ActualBudgetPrior YearSales10001200800COGS400600300Gross margin600600500Operating costs500450400Profit before tax100150100 Pls advise.

Anonymous

Not applicable

7 years ago

Solved

How to add variance column to matrix

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.

Need Help

v-juanli-msft
7 years ago
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.

5 Replies

v-juanli-msft

Community Support

7 years ago

Hi Anonymous

You need to re-create the data model.

Open Edit queries,

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"

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.

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.

How to add variance column to matrix.pbix91 KB

Anonymous
Not applicable
7 years ago
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
  Community Support
  7 years ago
  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.

Nars_eci
New Member
1 year ago
Did you find a good solution?

Forum Discussion

How to add variance column to matrix

5 Replies

Related content

Budget, actual and variance matrix

Add Variance Column to Matrix

Simple Matrix with Variance

Variance column for Dynamically selected columns

Matrix Column Widths