Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
HarpalsinhZala
New Member

Find Top 5 cost per year with vendor name

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:

YearVendor 1Cost 1Vendor 2Cost 2Vendor 3Cost 3Vendor 4Cost 4
2020A25Q54A547D78
2021B25C25E856C45
2020C27D157D32B15
2022E200B985C54B48
2022D159E148B15E98
2021A48A258D57A48

 

OUTPUT:

   

YEARVENDORCOST
2020A547
2020D157
2020D78
2020Q54
2020D32
2021E856
2021A258
2021D57
2021A48
2021A48

 

Please help me to find a way out of it.

Thanks

Harpalsinh Zala

1 ACCEPTED SOLUTION
v-yiruan-msft
Community Support
Community Support

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"

yingyinr_0-1630052646538.png

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
)

yingyinr_1-1630052791699.png

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]
    )
)

yingyinr_2-1630053862295.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
HarpalsinhZala
New Member

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 " ?

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.

yingyinr_0-1630650718996.pngThen 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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

v-yiruan-msft
Community Support
Community Support

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"

yingyinr_0-1630052646538.png

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
)

yingyinr_1-1630052791699.png

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]
    )
)

yingyinr_2-1630053862295.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
lbendlin
Super User
Super User

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"

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors