CODEWITHSUNDEEP
exceldaxdata-analysispowerquerym
davit bolqvadze
davit bolqvadze

Reputation: 9

Max if with multiple criteria in Power query

I have table with sales

I want to find the Product for each SalesName with Maximum Revenue By Month.

And if this Value (Revenue) is > the 70% of TotalRevenue of SalesName By Month (Sum of all products sold during the month), then I need the Product Name.

Else I need to find the Product Names of 2 Larges Revenues during the month.

[Please see the attached image]

Upvotes: 0

Views: 1503

Answers (1)

horseyride
horseyride

Reputation: 21318

Sample solution below The hardest part would be getting top products. You do this by grouping and adding an index, then filtering. The rest is just merging all the tables together and adding a custom column to decide which answer to show

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"SalesName", type text}, {"Product", type text}, {"Month", type text}, {"Revenue", Int64.Type}}),

// find top 2 Product for Salesname/Month
#"Grouped Rows" = Table.Group(#"Changed Type", {"SalesName","Month"}, {{"All", each Table.AddIndexColumn(Table.Sort(_,{{"Revenue", Order.Descending}}),"Index",1,1), type table}}),
#"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"Product", "Revenue", "Index"}, {"Product", "Revenue", "Index"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded All", each ([Index] =1 or [Index]=2)),
// Combine the two Products into one row separated by a ;
Top2= Table.Group( #"Filtered Rows", {"SalesName", "Month"}, {{"Top2", each Text.Combine([Product], "; "), type text}}),

//70% of Revenue for Salesname/Month
Percent70 = Table.Group(#"Changed Type", {"SalesName", "Month"}, {{"70%Revenue", each .7*List.Sum([Revenue]), type number}}),

//Top product each month for SaleName
#"Filtered Rows2" = Table.SelectRows(#"Expanded All", each [Index] =1),

// Merge in Top2
#"Merged Queries" = Table.NestedJoin(#"Filtered Rows2",{"SalesName", "Month"},Top2,{"SalesName", "Month"},"Top2",JoinKind.LeftOuter),
#"Expanded Top2" = Table.ExpandTableColumn(#"Merged Queries", "Top2", {"Top2"}, {"Top2"}),

// Merge in 70% Revenue
#"Merged Queries2" = Table.NestedJoin(#"Expanded Top2",{"SalesName", "Month"},Percent70,{"SalesName", "Month"},"70Percent",JoinKind.LeftOuter),
#"Expanded 70Percent" = Table.ExpandTableColumn(#"Merged Queries2", "70Percent", {"70%Revenue"}, {"70%Revenue"}),

// Compare revenue to 70% revenue and pick either Product or top 2 Products
#"Added Custom" = Table.AddColumn(#"Expanded 70Percent", "Custom", each if [Revenue]>[#"70%Revenue"] then [Product] else [Top2]),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index", "Top2", "70%Revenue"})

in #"Removed Columns"

Upvotes: 1

Related Questions