Biggest sales product year over year with Power BI

Question

I have a sales table of 5 products for multiple years and regions. I am struggling to get the biggest sales product year over year with Power BI. I know that I can group by year and get the total sales of each product, but what is the best way to extract which product had the biggest sales year over year? I have created a sample table:

Year	Region	Product A	Product B	Product C	Product D	Product E
2019	America	65	71	40	80	24
2020	America	50	56	36	44	45
2021	America	80	44	48	75	49
2022	America	90	65	55	52	99
2019	Asia	45	20	23	73	51
2020	Asia	55	33	54	52	50
2021	Asia	36	26	70	35	48
2022	Asia	23	64	77	28	28
2019	Europe	66	31	70	51	88
2020	Europe	56	66	8	60	53
2021	Europe	70	30	13	78	71
2022	Europe	89	78	45	67	80

PS: I have missing data in some of the cells, I am using null for those sells, would that change the calculation?

Answer 1

See if this works for you

EDIT: It finds the Product in each year that has the greatest growth between that year and the next year ignoring region

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Region", type text}, {"Product A", Int64.Type}, {"Product B", Int64.Type}, {"Product C", Int64.Type}, {"Product D", Int64.Type}, {"Product E", Int64.Type}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Year", "Region"}, "Attribute", "Value"),
#"Grouped Rows1" = Table.Group(#"Unpivoted Other Columns", {"Year", "Attribute"}, {{"Value", each List.Sum([Value]), type number}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows1", "NextYear", each [Year]+1),
#"Merged Queries" = Table.NestedJoin(#"Added Custom", {"Attribute", "NextYear"},  #"Added Custom", {"Attribute", "Year"}, "Added Custom", JoinKind.LeftOuter),
#"Expanded Added Custom1" = Table.ExpandTableColumn(#"Merged Queries", "Added Custom", {"Value"}, {"Next.Value"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Added Custom1", each ([Next.Value] <> null)),
#"Added Custom1" = Table.AddColumn(#"Filtered Rows", "Diff", each if [Next.Value]=null then null else [Next.Value]-[Value]),
#"Grouped Rows" = Table.Group(#"Added Custom1", {"Year"}, {{"data", each Table.LastN(Table.Sort(_,{{"Diff", Order.Ascending}}),1), type table }}),
#"Expanded data" = Table.ExpandTableColumn(#"Grouped Rows", "data", {"NextYear","Attribute" }, {"NextYear","Attribute"})
in  #"Expanded data"

It finds the Product in each year/region that has the greatest growth between that year and the next year

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Region", type text}, {"Product A", Int64.Type}, {"Product B", Int64.Type}, {"Product C", Int64.Type}, {"Product D", Int64.Type}, {"Product E", Int64.Type}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Year", "Region"}, "Attribute", "Value"),
#"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "NextYear", each [Year]+1),
#"Merged Queries" = Table.NestedJoin(#"Added Custom", {"Region", "NextYear"}, #"Added Custom", {"Region", "Year"}, "Added Custom", JoinKind.LeftOuter),
#"Expanded Added Custom" = Table.ExpandTableColumn(#"Merged Queries", "Added Custom", {"Value"}, {"Added Custom.Value"}),
#"Added Custom1" = Table.AddColumn(#"Expanded Added Custom", "Diff", each if [Added Custom.Value]=null then null else [Added Custom.Value]-[Value]),
#"Grouped Rows" = Table.Group(#"Added Custom1", {"Region", "Year"}, {{"data", each Table.LastN(Table.Sort(_,{{"Diff", Order.Ascending}}),1), type table }}),
#"Expanded data" = Table.ExpandTableColumn(#"Grouped Rows", "data", {"NextYear","Attribute", "Diff"}, {"NextYear","Attribute", "Diff"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded data", each ([Diff] <> null))
in  #"Filtered Rows"