cancel
Showing results for
Did you mean:
Helper IV

## calculating min and max values with filter

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
1 ACCEPTED SOLUTION
Super User IV

@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! 🙄

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

4 REPLIES 4
Super User IV

@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]))

Proud to be a Super User!

Helper IV

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!

Super User IV

@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! 🙄

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Super User IV
``````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])
``````

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Announcements

#### Happy New Year from Power BI

This is a must watch for a message from Power BI!