Find Top 5 cost per year with vendor name

[HarpalsinhZala]

Hi Folks,

I have a requirement regarding top 5 sales of data.My dataset is sharepoint online list, here share replica of it. I want to show top 5 sales but my sales data for a year are stored in rows.

DataSet:

Year	Vendor 1	Cost 1	Vendor 2	Cost 2	Vendor 3	Cost 3	Vendor 4	Cost 4
2020	A	25	Q	54	A	547	D	78
2021	B	25	C	25	E	856	C	45
2020	C	27	D	157	D	32	B	15
2022	E	200	B	985	C	54	B	48
2022	D	159	E	148	B	15	E	98
2021	A	48	A	258	D	57	A	48

OUTPUT:

YEAR	VENDOR	COST
2020	A	547
2020	D	157
2020	D	78
2020	Q	54
2020	D	32
2021	E	856
2021	A	258
2021	D	57
2021	A	48
2021	A	48

Please help me to find a way out of it.

Thanks

Harpalsinh Zala

[HarpalsinhZala]

Hi @v-yiruan-msft 

I have tried a solution using calculated table, it working but there I am facing some issues. 

Those are shared here,

1.  I have some slicers(Data Filters) based on column values of Original Table(Parent table), so I am unable to apply those on my TOP 5 Solution, because we achieve this using calculated table.
2. I need to show data in top 5 in order means first data of year 2020 with rank 1-5 then for 2021 with 1-5 rank.. in this manner.

Colud you please give more idea about solution you provided in "advanced query editor " ?

[v-yiruan-msft]

Hi @HarpalsinhZala ，

You can use the first method in my previous post: first convert multiple vendor and cost columns into two columns (vendor and cost) with multiple rows in the Power Query Editor as follows.

Then create a measure to get the rank, and set a visual level filter to get the top five costs.You can download the attachment in my previous post to learn more about the details of how to do this.s.

Best Regards

[lbendlin]

The way you describe it you will want to look up "RANKX with measures"

[v-yiruan-msft · Accepted solution]

Hi @HarpalsinhZala ,

I created a sample pbix file (see attachment) for you, please check whether that is what you want. 

Firstly, you need to convert the original table as the one in below screenshot in Powe Query Editor:

let
    Table = let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VY87EsAgCETvYm0BRCKW5nOA1I73v0aIQiYplsFlfWJrgYAgxFBVxFouFSdzOGWthypL6HGkUU+bp3dvTpXwalZiT4OHHITs3UJGwjdNRiIAmxXxZ8ZSj5XkG5/MYhdRhy90WuW3eZ0A/7AYYSxls95v", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Year = _t, #"Vendor 1" = _t, #"Cost 1" = _t, #"Vendor 2" = _t, #"Cost 2" = _t, #"Vendor 3" = _t, #"Cost 3" = _t, #"Vendor 4" = _t, #"Cost 4" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Vendor 1", type text}, {"Cost 1", Int64.Type}, {"Vendor 2", type text}, {"Cost 2", Int64.Type}, {"Vendor 3", type text}, {"Cost 3", Int64.Type}, {"Vendor 4", type text}, {"Cost 4", Int64.Type}}),
    res = List.TransformMany(Table.ToRows( #"Changed Type"),each List.Split(List.Skip(_,1),2),(x,y)=>List.FirstN(x,1)&y)
in
 #table({"Year","Vendor","Cost"},res),
    #"Changed Type" = Table.TransformColumnTypes(Table,{{"Cost", Int64.Type}})
in
    #"Changed Type"

Then create a measure as below to get the rank and filter the visual with the condition(rank <=5):

Rank = 
RANKX (
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[Year] = SELECTEDVALUE ( 'Table'[Year] )
    ),
    CALCULATE ( SUM ( 'Table'[Cost] ) ),
    ,
    DESC,
    DENSE
)

In addition, you can refer the content in the following thread by creating a calculated table to process the table with only 3 fields. Then same steps with the previous method: create the measure to get the rank and filter the visual with condition rank<=5...

Convert multiple column data into a column with multiple rows

New_Table = 
UNION (
    SELECTCOLUMNS (
        'Table2',
        "Year", 'Table2'[Year],
        "Vendor", 'Table2'[Vendor 1],
        "Cost", 'Table2'[Cost 1]
    ),
    SELECTCOLUMNS (
        'Table2',
        "Year", 'Table2'[Year],
        "Vendor", 'Table2'[Vendor 2],
        "Cost", 'Table2'[Cost 2]
    ),
    SELECTCOLUMNS (
        'Table2',
        "Year", 'Table2'[Year],
        "Vendor", 'Table2'[Vendor 3],
        "Cost", 'Table2'[Cost 3]
    ),
    SELECTCOLUMNS (
        'Table2',
        "Year", 'Table2'[Year],
        "Vendor", 'Table2'[Vendor 4],
        "Cost", 'Table2'[Cost 4]
    )
)

Best Regards

[lbendlin]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VY87EsAgCETvYm0BRCKW5nOA1I73v0aIQiYplsFlfWJrgYAgxFBVxFouFSdzOGWthypL6HGkUU+bp3dvTpXwalZiT4OHHITs3UJGwjdNRiIAmxXxZ8ZSj5XkG5/MYhdRhy90WuW3eZ0A/7AYYSxls95v", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Year = _t, #"Vendor 1" = _t, #"Cost 1" = _t, #"Vendor 2" = _t, #"Cost 2" = _t, #"Vendor 3" = _t, #"Cost 3" = _t, #"Vendor 4" = _t, #"Cost 4" = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
    #"Renamed Columns3" = Table.RenameColumns(#"Added Index",{{"Index", "Line"}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Renamed Columns3", {"Year","Line"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Split Column by Delimiter",{{"Attribute.2", "Index"}}),
    #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([Attribute.1] = "Vendor")),
    #"Renamed Columns1" = Table.RenameColumns(#"Filtered Rows",{{"Value", "Vendor"}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Renamed Columns1",{"Year", "Line", "Index", "Vendor"}),
    #"Filtered Rows2" = Table.SelectRows(#"Renamed Columns", each ([Attribute.1] = "Cost")),
    #"Renamed Columns2" = Table.RenameColumns(#"Filtered Rows2",{{"Value", "Cost"}}),
    #"Removed Other Columns2" = Table.SelectColumns(#"Renamed Columns2",{"Year","Line", "Index", "Cost"}),
    Res=Table.Join(#"Removed Other Columns",{"Year","Index","Line"},#"Removed Other Columns2",{"Year","Index","Line"},JoinKind.Inner),
    #"Removed Other Columns1" = Table.SelectColumns(Res,{"Year", "Vendor", "Cost"})
in
    #"Removed Other Columns1"