Excel-like operations between values from different Table rows/columns

[leithold]

Hi, I'm quite new to Power BI and I'm trying to build a dashboard for my project. I have no problem doing it in excel since I got the basics of it. I have three tables whose structure is shown as below.:

Targets table

Year	Month	Category	Product	Region	Territory	Value
2018	Jan	A	A1	Asia	Japan	1000
2018	Jan	A	A1	Asia	China	1500
2018	Jan	A	A2	Asia	Japan	1000
2018	Jan	A	A2	Asia	China	1500
2018	Jan	A	A1	Europe	Germany	1000
2018	Jan	A	A1	Europe	France	1500
2018	Jan	A	A2	Europe	Germany	1000
2018	Jan	A	A2	Europe	France	1500
2018	Jan	B	B1	Asia	Japan	1000
2018	Jan	B	B1	Asia	China	1500
2018	Jan	B	B2	Asia	Japan	1000
2018	Jan	B	B2	Asia	China	1500
2018	Jan	B	B1	Europe	Germany	1000
2018	Jan	B	B1	Europe	France	1500
2018	Jan	B	B2	Europe	Germany	1000
2018	Jan	B	B2	Europe	France	1500
2018	Feb	A	A1	Asia	Japan	1000
2018	Feb	A	A1	Asia	China	1500
2018	Feb	A	A2	Asia	Japan	1000
2018	Feb	A	A2	Asia	China	1500
2018	Feb	A	A1	Europe	Germany	1000
2018	Feb	A	A1	Europe	France	1500
2018	Feb	A	A2	Europe	Germany	1000
2018	Feb	A	A2	Europe	France	1500
2018	Feb	B	B1	Asia	Japan	1000
2018	Feb	B	B1	Asia	China	1500
2018	Feb	B	B2	Asia	Japan	1000
2018	Feb	B	B2	Asia	China	1500
2018	Feb	B	B1	Europe	Germany	1000
2018	Feb	B	B1	Europe	France	1500
2018	Feb	B	B2	Europe	Germany	1000
2018	Feb	B	B2	Europe	France	1500

2018 Transactions table

TransactionID	Date	Category	Product	Region	Territory	Value
2018-0000000001	1/1/2018	A	A1	Asia	Japan	2
2018-0000000002	1/1/2018	A	A1	Asia	Japan	0.5
2018-0000000003	1/1/2018	A	A2	Asia	China	0.75
2018-0000000004	1/1/2018	A	A2	Europe	Germany	3

2017 Transactions table

TransactionID	Date	Category	Product	Region	Territory	Value
2017-0000000001	1/1/2017	A	A1	Asia	Japan	500
2017-0000000002	1/1/2018	A	A1	Europe	France	0.5
2017-0000000003	1/1/2018	A	A2	Europe	Germany	0.75
2017-0000000004	1/1/2018	A	A2	Europe	France	3

Basically, I want to compare my current 2018 sales versus target, as well as growth compared to last year. From 'YEAR' drilled down up to 'TERRITORY'. 

I want to produce a matrix like this:

Year	Month	Category	Product	Region	Territory	2018 Target	2018 Sales	2017 Sales	2018 ACH	2018 GR
2018	Jan	A	A1	Asia	Japan	1000	2	500	0.20%	-99.60%

I can easily do it in Excel since this only involves SUMIFS and some formula. When I imported the tables on Power BI, I'm quite lost on how can I transform SUMIFS to Power BI. I've read about measures but I'm quite lost since I need to drill down up from YEAR up to TERRITORY level.

Hope you guys can help me on this. Much appreciated.

[smpa01 · Accepted solution]

Target table

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Month", type text}, {"Category", type text}, {"Product", type text}, {"Region", type text}, {"Territory", type text}, {"Value", Int64.Type}}),
    #"Inserted Merged Column" = Table.AddColumn(#"Changed Type", "Product-Region-Territory", each Text.Combine({[Product], [Region], [Territory]}, "-"), type text),
    #"Inserted Merged Column1" = Table.AddColumn(#"Inserted Merged Column", "Merged", each Text.Combine({[#"Product-Region-Territory"], [Month]}, "-"), type text)
in
    #"Inserted Merged Column1"

2018 T table

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"TransactionID", type text}, {"Date", type datetime}, {"Category", type text}, {"Product", type text}, {"Region", type text}, {"Territory", type text}, {"Value", type number}}),
    #"Inserted Merged Column" = Table.AddColumn(#"Changed Type", "Product-Region-Territory", each Text.Combine({[Product], [Region], [Territory]}, "-"), type text),
    #"Added Custom" = Table.AddColumn(#"Inserted Merged Column", "Custom", each Date.From([Date])),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type date}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type1", "Custom", "Custom - Copy"),
    #"Split Column by Delimiter" = Table.SplitColumn(Table.TransformColumnTypes(#"Duplicated Column", {{"Custom - Copy", type text}}, "en-US"), "Custom - Copy", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Custom - Copy.1", "Custom - Copy.2", "Custom - Copy.3"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom - Copy.1", Int64.Type}, {"Custom - Copy.2", Int64.Type}, {"Custom - Copy.3", Int64.Type}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type2", "Custom.1", each #date([#"Custom - Copy.3"],[#"Custom - Copy.1"],[#"Custom - Copy.2"])),
    #"Changed Type3" = Table.TransformColumnTypes(#"Added Custom1",{{"Custom.1", type date}}),
    #"Added Custom2" = Table.AddColumn(#"Changed Type3", "Custom.2", each Date.MonthName([Custom.1])),
    #"Inserted Text Range" = Table.AddColumn(#"Added Custom2", "Text Range", each Text.Middle([Custom.2], 0, 3), type text),
    #"Inserted Merged Column1" = Table.AddColumn(#"Inserted Text Range", "Merged", each Text.Combine({[#"Product-Region-Territory"], [Text Range]}, "-"), type text)
in
    #"Inserted Merged Column1"

2017 T table

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"TransactionID", type text}, {"Date", type datetime}, {"Category", type text}, {"Product", type text}, {"Region", type text}, {"Territory", type text}, {"Value", type number}}),
    #"Inserted Merged Column" = Table.AddColumn(#"Changed Type", "Product-Region-Territory", each Text.Combine({[Product], [Region], [Territory]}, "-"), type text),
    #"Added Custom" = Table.AddColumn(#"Inserted Merged Column", "Custom", each Date.From([Date])),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type date}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type1", "Custom", "Custom - Copy"),
    #"Split Column by Delimiter" = Table.SplitColumn(Table.TransformColumnTypes(#"Duplicated Column", {{"Custom - Copy", type text}}, "en-US"), "Custom - Copy", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Custom - Copy.1", "Custom - Copy.2", "Custom - Copy.3"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom - Copy.1", Int64.Type}, {"Custom - Copy.2", Int64.Type}, {"Custom - Copy.3", Int64.Type}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type2", "Custom.1", each #date([#"Custom - Copy.3"],[#"Custom - Copy.1"],[#"Custom - Copy.2"])),
    #"Changed Type3" = Table.TransformColumnTypes(#"Added Custom1",{{"Custom.1", type date}}),
    #"Added Custom2" = Table.AddColumn(#"Changed Type3", "Custom.2", each Date.MonthName([Custom.1])),
    #"Inserted Text Range" = Table.AddColumn(#"Added Custom2", "Text Range", each Text.Middle([Custom.2], 0, 3), type text),
    #"Inserted Merged Column1" = Table.AddColumn(#"Inserted Text Range", "Merged", each Text.Combine({[#"Product-Region-Territory"], [Text Range]}, "-"), type text)
in
    #"Inserted Merged Column1"

Final Table

let
    Source = Table.NestedJoin(Target,{"Merged"},#"2018T",{"Merged"},"2018T",JoinKind.LeftOuter),
    #"Expanded 2018T" = Table.ExpandTableColumn(Source, "2018T", {"Value"}, {"2018T.Value"}),
    #"Merged Queries" = Table.NestedJoin(#"Expanded 2018T",{"Merged"},#"2017T",{"Merged"},"2017T",JoinKind.LeftOuter),
    #"Expanded 2017T" = Table.ExpandTableColumn(#"Merged Queries", "2017T", {"Value"}, {"2017T.Value"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded 2017T",{"Year", "Month", "Category", "Product", "Region", "Territory", "Value", "2018T.Value", "2017T.Value", "Merged"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"Value", "2018 Target"}, {"2018T.Value", "2018 Sales"}, {"2017T.Value", "2017 Sales"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Merged"}),
    #"Inserted Division" = Table.AddColumn(#"Removed Columns", "Division", each [2018 Sales] / [2018 Target], type number),
    #"Changed Type" = Table.TransformColumnTypes(#"Inserted Division",{{"Division", Percentage.Type}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Changed Type",{{"Division", "2018 ACH"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns1", "2018 GR", each ([2018 Sales]-[2017 Sales])/[2017 Sales]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"2018 GR", Percentage.Type}})
in
    #"Changed Type1"