Forum Discussion

CoreyP's avatar
CoreyP
Solution Sage
6 years ago

A seemingly complex task...

Hey all,   I've got what might be a fun one for all you experts. Here's what I'm trying to do:   If any of you have a background in warehouse management, you might get this right off the bat. I'm...
Need Help
kentyler's avatar
kentyler
Solution Sage
6 years ago

A measure will need some way of calculating its "previous" row, as there is no built in concept of row numbers in power bi. Sometimes people use dates for this, often they use an index column.

An index column would imply that all your pallets were sorted in the correct order.

Then your measure can do a sum() of all the rows with the index > the row it is executing on

which sort of implies that you have filtered the records to contain pallets with that part

CoreyP's avatar
CoreyP
Solution Sage
6 years ago

I created a "Pallet Ranking" by going into query editor, and sorting first by SKU, then LOT, then FIFO date, and adding an Index column. 

 

Seems to work. So how would I create the running total measure? I tried the following but it did not work:

 

Cumulative Qty =
CALCULATE (
SUM( 'Inventory'[ONHAND_QTY] ),
FILTER (
ALL ( 'Inventory'[Pallet Rank] ),
'Inventory'[Pallet Rank] <= MAX ( ( 'Inventory'[Pallet Rank] ) )
)
)
  • kentyler's avatar
    kentyler
    Solution Sage
    6 years ago

     

    I think you want to store the current value before you do calculate
     
    Cumulative Qty =
    var  cur_index = max('inventory'[Pallet rank])

     

     
    CALCULATE (
    SUM( 'Inventory'[ONHAND_QTY] ),
    FILTER (
    ALL ( 'Inventory'[Pallet Rank] ),
    'Inventory'[Pallet Rank] <= cur_index )
    )
    )
    • CoreyP's avatar
      CoreyP
      Solution Sage
      6 years ago

      That doesn't seem to be working. Just returns 1 for every value. Do we have to take item_no into context somehow?

    • kentyler's avatar
      kentyler
      Solution Sage
      6 years ago

      If you just return the current index, does it show the correct number ?

      • CoreyP's avatar
        CoreyP
        Solution Sage
        6 years ago

        This is what it returns:

        Using this measure:

        Cumulative Qty =
        VAR cur_index = MAX('Inventory'[Pallet Rank])
         
        RETURN
        CALCULATE (
        SUM( 'Inventory'[ONHAND_QTY] ),
        FILTER (
        ALL('Inventory'[Pallet Rank]),
        'AIRMInventory'[Pallet Rank] <= cur_index ))