Forum Discussion

pstanek's avatar
pstanek
Post Patron
10 years ago

MAX MIN

I have table with columns

 

Office -Product-  Stock Value.

I need to create  Product - MIN Stock - Office with min stock - Max stock - Office with max stock

 

I try to avoid to order new products for one office, when another has too many products.

 

For example product_i was sold out by one office, another could sell only few product.

 

Do you have any idea?

 

Another step should be identifying products with largest Max stock - Min Stock.

Need Help
Show and Tell

12 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    10 years ago

    Min Stock = MIN(StockTable[Stock])

     

    Office with Min Stock = CALCULATE( LASTNONBLANK(StockTable[Office], 1),

    FILTER( StockTable, StockTable[Stock] = MIN(StockTable[Stock]))

     

    Max Stock = MAX(StockTable[Stock])

     

    Office with Max Stock = CALCULATE( LASTNONBLANK(StockTable[Office], 1),

    FILTER( StockTable, StockTable[Stock] = MAX(StockTable[Stock]))

    • pstanek's avatar
      pstanek
      Post Patron
      10 years ago

      Is LASTNONBLANK supported in direct query?

      • Anonymous's avatar
        Anonymous
        Not applicable
        10 years ago

        pstanek I don't think so. If your offices are numbered you could use MAX(StockTable[Office]) instead of LASTNONBLANK. If Office is a text field maybe something like CONCATENATE(StockTable[Office], "") I am not sure what to do about that. You need some way to get a single row value and LASTNONBLANK/FIRSTNONBLANK is the only way I know of for that.