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 product...
Need Help
Show and Tell
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.

Anonymous's avatar
Anonymous
Not applicable
10 years ago

Figured it out. I was close with CONCATENATE but not quite right.

 

Office with Min Stock = CALCULATE( CONCATENATEX(VALUES(StockTable[Office]), StockTable[Office],

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

 

  • pstanek's avatar
    pstanek
    Post Patron
    10 years ago

    Thanks, It works for some examples. 

     

      I have another idea, but I dont know , if it is possible.

     Can I sort table by levels? I have table within report. I would like to sort it by product, and second level by office. Is it possible?

     

    I need to find solution, when stock table exists only within report, but it is not in query. I mean it has been created  through relations.