Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
leithold
Regular Visitor

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

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

YearMonthCategoryProductRegionTerritoryValue
2018JanAA1AsiaJapan1000
2018JanAA1AsiaChina1500
2018JanAA2AsiaJapan1000
2018JanAA2AsiaChina1500
2018JanAA1EuropeGermany1000
2018JanAA1EuropeFrance1500
2018JanAA2EuropeGermany1000
2018JanAA2EuropeFrance1500
2018JanBB1AsiaJapan1000
2018JanBB1AsiaChina1500
2018JanBB2AsiaJapan1000
2018JanBB2AsiaChina1500
2018JanBB1EuropeGermany1000
2018JanBB1EuropeFrance1500
2018JanBB2EuropeGermany1000
2018JanBB2EuropeFrance1500
2018FebAA1AsiaJapan1000
2018FebAA1AsiaChina1500
2018FebAA2AsiaJapan1000
2018FebAA2AsiaChina1500
2018FebAA1EuropeGermany1000
2018FebAA1EuropeFrance1500
2018FebAA2EuropeGermany1000
2018FebAA2EuropeFrance1500
2018FebBB1AsiaJapan1000
2018FebBB1AsiaChina1500
2018FebBB2AsiaJapan1000
2018FebBB2AsiaChina1500
2018FebBB1EuropeGermany1000
2018FebBB1EuropeFrance1500
2018FebBB2EuropeGermany1000
2018FebBB2EuropeFrance1500

 

2018 Transactions table

TransactionIDDateCategoryProductRegionTerritoryValue
2018-00000000011/1/2018AA1AsiaJapan2
2018-00000000021/1/2018AA1AsiaJapan0.5
2018-00000000031/1/2018AA2AsiaChina0.75
2018-00000000041/1/2018AA2EuropeGermany

3

 

2017 Transactions table

TransactionIDDateCategoryProductRegionTerritoryValue
2017-00000000011/1/2017AA1AsiaJapan500
2017-00000000021/1/2018AA1EuropeFrance0.5
2017-00000000031/1/2018AA2EuropeGermany0.75
2017-00000000041/1/2018AA2EuropeFrance3

 

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:

 

YearMonthCategoryProductRegionTerritory2018 Target2018 Sales2017 Sales2018 ACH2018 GR
2018JanAA1AsiaJapan100025000.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.

 

1 ACCEPTED SOLUTION
smpa01
Super User
Super User

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"
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

View solution in original post

1 REPLY 1
smpa01
Super User
Super User

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"
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

Helpful resources

Announcements
March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.