getting lowest value and highest value

[iamprajot]

Hi, I need to know how to get the lowest ShippingPrice and the highest ShippingPrice for a ProductID. Thanks

ProductID	ShippingPrice	Lowest Price	Highest Price
5	32.38
6	11.61
4	65.83
3	41.34
4	51.30
3	58.17
5	22.98
9	148.33
3	13.97
4	81.91
1	140.51
4	3.25
4	55.09
4	3.05
8	48.29
9	146.06
6	3.67
2	55.28
3	25.73
4	208.58
8	66.29
5	4.56
1	136.54
6	4.54

[v-qiuyu-msft · Accepted solution]

Hi @iamprajot,

In addition, you can also create measures or calculated columns use ALLEXCEPT Function below:

Lowest Price = CALCULATE(MIN('Table2'[ShippingPrice]),ALLEXCEPT(Table2,'Table2'[ProductID]))

Highest Price = CALCULATE(MAX('Table2'[ShippingPrice]),ALLEXCEPT(Table2,'Table2'[ProductID]))

Best Regards,
Qiuyun Yu

[iamprajot]

Much appreciated, thanks.

[malagari]

This is perfect use for the EARLIER function.

Lowest Price = CALCULATE( MIN([Shipping Price]), 
                          FILTER(Table, [ProductID] = EARLIER([ProductID]))
               )

And similar, you can switch MIN to MAX for the Highest Price for that Product.

The FILTER component will return a table of similar ProductIDs to the row being calculated on, and then the MIN function will return the lowest shipping price for this filtered table. 

[iamprajot]

Unfortunatly it shows an error and highlights the Red part

EARLIER/EARLIEST refers to an earlier row context which doesn't exist.

Lowest Price = CALCULATE( MIN([Shipping Price]), 
                          FILTER(Table, [ProductID] = EARLIER([ProductID]))
               )