Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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"
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |