calculating min and max values with filter

[DeBIe]

Hello all,

I need to write something in DAX, but I can't figure out how to get the context right. Hopefully, you can help me.

I have a set of bills from different shops. I need to add a column that displays the minimum and maximum amount that has been paid to that supplier. Not from one specific bill, but from all bills together. Down below marked in thick black are the columns that I would like to add.

What the logic should do is: lookup all invoices from the supplier name in this row and find the min and max amount that has been paid. I have tried with CALCULATE(min(Table[Amount]),Table[Supplier]=Table[Supplier]

Bill Number	Supplier	Amount	Min Amount Supplier	Max Amount Supplier
1	Wallmart	10	10	500
2	Harry's	50	50	75
3	Wallmart	500	10	500
4	Harry's	75	50	75
5	Wallmart	250	10	500
6	Harry's	60	50	75

[amitchandak]

@DeBIe ,Try

Min= Min('Table'[Amount])
Max = Max('Table'[Amount])

or
Min= CALCUALTE(Min('Table'[Amount]),allexcept(Table, Table[Supplier]))
Max = CALCUALTE(Max('Table'[Amount]),allexcept(Table, Table[Supplier]))

[DeBIe]

Hello @amitchandak ,

Thank you, the calculate formula works.

I need one additional filter to add to the logic. I also have a column with the district/region. Which means supplier Wallmart can appear in district A, B, C, etc. When I calculate the min and max amount, it needs to be the amount of that specific district. How can I add this to the existing formula? I think something like:

Min= CALCUALTE(Min('Table'[Amount]),allexcept(Table, Table[Supplier]) ,allexcept(Table, Table[District])

Thank you!

[Greg_Deckler · Accepted solution]

@DeBIe - Try:

Column =
  VAR __Supplier = [Supplier]
  VAR __District = [District]
  MINX(
    FILTER('Table',[Supplier]=__Supplier && [District]=__District),
    [Amount]
  )

Measure =
  VAR __Supplier = MAX([Supplier])
  VAR __District = MAX([District])
  MINX(
    FILTER(ALL('Table'),[Supplier]=__Supplier && [District]=__District),
    [Amount]
  )

Sorry I forgot the ALLEXCEPT versus ALL earlier! 🙄

[Greg_Deckler]

@DeBIe - 

Min Amount Supplier Column = MIN('Table'[Amount])

Max Amount Supplier Column = MAX('Table'[Amount])

Min Amount Supplier Measure = MINX(ALL('Table'),[Amount])

Max Amount Supplier Column = MAXX(ALL('Table'),[Amount])