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