Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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"
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"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |