Forum Discussion

Gerald23's avatar
Gerald23
Helper I
5 years ago
Solved

Top 3 Lowest value's

Hi Everyone,

 

I have a table that looks like this:

 

Product NumberOnline ShopPrice
001a.com5,44
001b.com4,89
001c.com6,40
001d.com11,95
001r.com9.82
002a.com6,75
002g.com14,65
002z.com7,37
002p.com8,95

 

From this table i would like to get the 3 lowest values for each Product Number and also show the Online Shop that belongs to that value. Sorted by the lowest value first.

 

So it should look like this:

 

Product NumberOnline ShopPrice
001b.com4.89
001a.com5,44
001c.com6,40
002a.com6,75
002z.com7,37
002p.com8,95

 

Does anyone know how I could achieve this in Power BI?

Need Help
  • mahoneypat's avatar
    mahoneypat
    5 years ago

    Here is one way to do it with a DAX measure in a Table visual with your Product Number and Online Store columns

     

    Lowest 3 Price =
    VAR thisProduct =
        MIN ( 'Price'[Product Number] )
    VAR lowest3 =
        TOPN (
            3,
            FILTER ( ALL ( 'Price' ), 'Price'[Product Number] = thisProduct ),
            'Price'[Price], ASC
        )
    VAR result =
        CALCULATE ( SUM ( 'Price'[Price] ), KEEPFILTERS ( lowest3 ) )
    RETURN
        result

     

    Pat

     

3 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    5 years ago

    Here is one way to do it with a DAX measure in a Table visual with your Product Number and Online Store columns

     

    Lowest 3 Price =
    VAR thisProduct =
        MIN ( 'Price'[Product Number] )
    VAR lowest3 =
        TOPN (
            3,
            FILTER ( ALL ( 'Price' ), 'Price'[Product Number] = thisProduct ),
            'Price'[Price], ASC
        )
    VAR result =
        CALCULATE ( SUM ( 'Price'[Price] ), KEEPFILTERS ( lowest3 ) )
    RETURN
        result

     

    Pat

     

    • Gerald23's avatar
      Gerald23
      Helper I
      5 years ago

      Thanks this solution works for me ğŸ˜€

  • Jakinta's avatar
    Jakinta
    Solution Sage
    5 years ago

    You can try code below.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc07CoAwEATQu2wdhiRufmcJKWIEK1EsPb0iG9hq4DHM1ErWOjLUMc7jywBmambyKszIRfEQjtDlTdQ5lKD8Fi/IXtiry4gUFO9zhRG1P+IJS1J8Cef/s70=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Product Number" = _t, #"Online Shop" = _t, Price = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Price", type number}}),
    Grouped = Table.Group(ChangedType, {"Product Number"}, {{"Gr", each Table.FirstN(Table.Sort(_,{{"Price", Order.Ascending}}), 3 ), type table [Product Number=nullable text, Online Shop=nullable text, Price=nullable number]}}),
    Expanded = Table.ExpandTableColumn(Grouped, "Gr", {"Online Shop", "Price"})
in
    Expanded