Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi Everyone,
I have a table that looks like this:
Product Number | Online Shop | Price |
001 | a.com | 5,44 |
001 | b.com | 4,89 |
001 | c.com | 6,40 |
001 | d.com | 11,95 |
001 | r.com | 9.82 |
002 | a.com | 6,75 |
002 | g.com | 14,65 |
002 | z.com | 7,37 |
002 | p.com | 8,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 Number | Online Shop | Price |
001 | b.com | 4.89 |
001 | a.com | 5,44 |
001 | c.com | 6,40 |
002 | a.com | 6,75 |
002 | z.com | 7,37 |
002 | p.com | 8,95 |
Does anyone know how I could achieve this in Power BI?
Solved! Go to Solution.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Thanks this solution works for me 😀
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