Rank With Multiple Criteria

[Lewzerrrr]

Hi,

This has been asked a lot but I can't adapt solutions to my needs.

I need to rank by brand the top & bottom variance but only if the sales & plan <> 0.

Sample data below with expected results

Brand	Sales	Plan	Var	Top Rank	Bottom Rank
TS	7,374	6,287	1,087	2	12
TS	15,849	16,798	-949	8	6
TM	3,085	3,549	-464	3	6
TM	9,498	11,716	-2,218	7	2
TS	3,666	4,165	-499	4	10
TS	5,026	6,091	-1,065	9	5
TS	12,042	12,987	-945	7	7
TS	4,059	5,677	-1,618	11	3
TM	2,294	4,121	-1,827	6	3
TS	14,603	15,349	-746	6	8
TM	0	0	0	-	-
TM	842	1,219	-377	1	8
TM	0	0	0	-	-
TS	11,814	13,996	-2,182	12	2
TS	6,930	7,996	-1,066	10	4
TS	0	0	0	-	-
TM	3,374	5,832	-2,458	8	1
TS	13,091	12,184	907	3	11
TS	41,375	40,040	1,335	1	13
TM	5,802	6,408	-606	4	5
TM	4,325	4,702	-377	2	7
TM	2,082	2,830	-748	5	4
TS	21,470	22,101	-631	5	9
TS	7,115	12,438	-5,323	13	1

<colgroup><col width="64" span="6" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[lrobbo314]

For extra credit, here is a Power Query version to get the same results.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Brand", type text}, {"Sales", Int64.Type}, {"Plan", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Index", "Brand", "Sales", "Plan"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Index", "Order"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Var", each [Sales]-[Plan]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each [Sales]=0 and [Plan] =0),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Custom] = false)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom"}),
    #"Sorted Rows" = Table.Sort(#"Removed Columns",{{"Brand", Order.Ascending}, {"Var", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Brand"}, {{"Count", each _, type table}, {"Rows", each Table.RowCount(_), type number}}),
    #"Added Custom2" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"Bottom",1,1)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom2", "Custom", {"Order", "Sales", "Plan", "Var", "Bottom"}, {"Order", "Sales", "Plan", "Var", "Bottom"}),
    #"Reordered Columns1" = Table.ReorderColumns(#"Expanded Custom",{"Order", "Brand", "Count", "Rows", "Sales", "Plan", "Var", "Bottom"}),
    #"Added Custom3" = Table.AddColumn(#"Reordered Columns1", "Top", each [Rows]-[Bottom]+1),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom3",{"Count", "Rows"}),
    #"Sorted Rows1" = Table.Sort(#"Removed Columns1",{{"Order", Order.Ascending}}),
    #"Reordered Columns2" = Table.ReorderColumns(#"Sorted Rows1",{"Order", "Brand", "Sales", "Plan", "Var", "Top", "Bottom"}),
    #"Removed Columns2" = Table.RemoveColumns(#"Reordered Columns2",{"Order"})
in
    #"Removed Columns2"

Brand	Sales	Plan	Var	Top	Bottom
TS	7374	6287	1087	2	12
TS	15849	16798	-949	8	6
TM	3085	3549	-464	3	6
TM	9498	11716	-2218	7	2
TS	3666	4165	-499	4	10
TS	5026	6091	-1065	9	5
TS	12042	12987	-945	7	7
TS	4059	5677	-1618	11	3
TM	2294	4121	-1827	6	3
TS	14603	15349	-746	6	8
TM	842	1219	-377	2	7
TS	11814	13996	-2182	12	2
TS	6930	7996	-1066	10	4
TM	3374	5832	-2458	8	1
TS	13091	12184	907	3	11
TS	41375	40040	1335	1	13
TM	5802	6408	-606	4	5
TM	4325	4702	-377	1	8
TM	2082	2830	-748	5	4
TS	21470	22101	-631	5	9
TS	7115	12438	-5323	13	1

<tbody>
</tbody>

The first step of adding the table to Power Query, I only added Brand, Sales, and Plan. Var is a column that is created in the code.