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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Gerald23
Helper I
Helper I

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?

1 ACCEPTED SOLUTION
mahoneypat
Employee
Employee

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

mahoneypat_0-1625609490035.png

 

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

3 REPLIES 3
mahoneypat
Employee
Employee

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

mahoneypat_0-1625609490035.png

 

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Thanks this solution works for me 😀

Jakinta
Solution Sage
Solution Sage

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors