none
Measure that retains Rounded up value for each ID and does the total later: Warehouse Case RRS feed

  • Question

  • Hello All,

    I am building a report on PowerBi to visualise some warehouse activities.

    Sorry for the long description but I tried to be as clear as possible as I am new to PowerBI so it is difficult for me to understand what it is doing. Also I would like to learn why to implement a certain solution to understand the software better and not just get a copy-paste solution.

    What I want to visualise is:

    • projection of incoming deliveries in n. of pallets for each day.

    • projection of stock in n. of pallets for each day

    This is how I am building it:

    • I have a data set with all the projected stock movements which includes: current stock level (at date 0), future requirements (for each day in the future), future incoming deliveries (for each day in the future)

    • I have another table (MRP) with some parameters that want to use for visualisations, filtering, etc... Some can be warehouse number or material type.

    The data set (Query "Forecast") has these columns (cannot share data due to company policy):

    • ID (text): unique code for a certain material. I use this to link it to the other table (MRP) with all the parameters that describe the materials. In table "Forecast" I have many instances for each ID (many stock movements during the period) while in MRP table I have 1 instance for each ID. (relationship 1 to many).

    • Movement type (text): can be Initial Stock, Requirements or Deliveries

    • Date (date): this is the date of which a certain stock movement for a certain ID will happen

    • Pallet calculation (decimal number) = quantity (in KG for example of a certain ID movement type at a certain date)/ quantity per pallet. It is a decimal number because we can also receive or store not full pallets.

    Below the 2 problems I am trying to solve. Can you please let me know what is wrong in the measures or if I should completely change approach?

    Problem n 1 > Incoming pallets

    What I want to obtain is a calculation for each ID at each date that sums up "pallet calculation" and rounds it up (If I receive 1,5 pallets this will actually take 2 pallets space). It is important that the calculation is done separately for each ID (for example if I receive 1,3 pallets of ID 1 and 1,4 pallets of ID 2 the total number of pallets should be 2 for ID 1 and 2 for ID 2, so a total of 4. If I first do the sum of all the IDs and then round it up it will give me 3 pallets instead of 4). So the step should be: 1 - do the sum for each ID, 2 - do the round up for each ID - 3 do the total with the previous rounded up values

    I tried with this measure in the table "forecast":

    Pallet DELIVERY = Calculate(ROUNDUP(SUM(Forecast[pallet calculation],0),VALUES(Forecast[ID])

    I have put the measure in a column chart, on the axis I have put the date and on the filter I have put the movement type that I need (Deliveries). I also did the same with a matrix to look at the data.

    • I noticed that while the calculation for each ID are correct, the total is wrong. I think the total rounds up the sum of all pallets calculation for each ID and at the end rounds it up. Instead I want to do the round up at each single ID and then sum up the total. I though that VALUES(Forecast[ID]) would solve this.

    • This measure doesn't take into account movement type in the measure. But I have put a filter on. Does this leads to mistakes?

    • Is it a problem that I do not include the date in the measure? I want to be able to build reports on daily level, weekly level and so on.

    • How can I make sure that it retains rounded up calculation for each material at each date?

    • I want to be able to use parameters from MRP table to do filtering on visualisations. E.g. I have the parameter type which can be RAW or PACK. and If needed I want to be able to filter it out, or put it in a stacked column chart without altering the calculation. This is also why it is important to retain the calculation for each ID. So I can build dashboard personalised that shows the right total depending on values from MRP table.

    Or maybe my approach is completely off :D

    Problem n 2 > stock projection of pallets

    The problem is similar to the one above but a bit more complex.

    I am afraid it is not working because of the same reason of problem 1: instead of doing round up for each single ID the round up is done at the cumulative level for the variable "pallet calculation".

    Here I want to do the cumulative sum of "pallet calculation" for all movement types (some are negative some are positive) over time and round it up to the integer for each ID. Then I can do the sum of each ID results to get the total value (with the needed filtering based on parameters from MRP table).

    The measure takes into account a few more things compared to problem 1:

    • stock projection can go negative (If I plan to consume more than what I receive) but pallets cannot be negative. So it should give 0 instead of negative number

    • cumulative value over time.

    Here is what I did with a measure in table Forecast:

    Pallet STOCK = Calculate(MAX(ROUNDUP(SUM(Forecast[pallet calculation]),0),0),FILTER(ALL(Forecast),Forecast[date]<=MAX(Forecast[date])),VALUES(Forecast[ID]))

    • I need to solve the problem of rounding up mentioned above few times.

    • I use this measure in a bar chart and I put date on the axis. Date comes from a different table with a list of dates. This is because sometimes a data point for an ID at a certain date can be missing and then it does not show up. e.g. I have stock movements on day 1,2,3,5. So there are no data points for this ID at day 4. This means that there will not be a stock projection for this ID at day 4 while there should be. Does this solution (of having this external table as axis in the graph) solve the issue?

    • Hope all the rest makes sense!

    Please let me know if something not clear. I am losing my head on this thing :D 


    Sunday, July 14, 2019 8:08 PM

Answers

  • Also another problem I am facing is (see sheet 2 lines highlighted in red) when I have a gap in the data and some dates are missing (here we jump from 16/08 to 21/08). I am afraid that at the date 17/08,18/08,... the data will now show up because there is no datapoint for date for that specific ID in the table Forecast. How can I avoid this?

    You can solve this by adding a "Date" table to your model. This date table should have a continuous series of dates covering all the dates in your forecast table.

    Then your stock projection measure would be something like the following:

    Pallet Forecast = 
    MAX(
        CALCULATE([Pallet Delivery] , filter(all('Date'[Date]),'Date'[Date] <= max('Date'[Date]))) 
        , 0
    )


    http://darren.gosbell.com - please mark correct answers

    • Marked as answer by TommyWhammy Tuesday, July 23, 2019 8:48 PM
    Monday, July 15, 2019 11:54 PM
    Moderator

All replies

  • So this looks like an order of operations issue. You are currently summing all the [Pallet Calculation] values, then doing the rounding, which works at the individual [ID] level, but not for the totals.

    A simplistic approach would be to do SUMX( Forecast, ROUNDUP( Forecast[Pallet Calculation] ) ) but the issue with that is that it would round up something like 0.4  + 0.4 for the same ID to 2 rather than adding them to 0.8 and rounding to 1 to work around this you could use a more complicated expression like the one below where I use the SUMMARIZE function to group by ID then round up, then I loop over all the IDs using SUMX and add those up

    Pallet Delivery =
    // Group by ID and round up [Pallet Calculation]
    VAR _roundupPallets = SUMMARIZE(VALUES(Forecast[ID]),[ID], "Rounded Pallets", ROUNDUP(SUM(Forecast[Pallet Calculation]),0))
    RETURN SUMX(_roundupPallets, [Rounded Pallets])

    I think this should solve your first issue. But I have to admit I'm not really clear on what your second issue is. If you could post a few rows of made up example data that might help. I'm guessing that you could probably put the IF expression inside the second argument to the SUMX above, but I'm not sure.


    http://darren.gosbell.com - please mark correct answers

    Sunday, July 14, 2019 11:22 PM
    Moderator
  • Hello Darren,

    This solution you proposed works really well for the problem number 1. Thank you very much!

    In problem n2 I want to do the same thing but cumulative over time. I want to do a stock projection.

    The steps should be:

    1 -  sum of all pallet calculation until a certain date for each ID. In my example above I did it with CALCULATE and adding FILTER(ALL(Forecast),Forecast[date]<=MAX(Forecast[date])),VALUES(Forecast[ID])

    2 - round up the value to the integer

    3- do the sum of rounded up values

    NOTE: stock projection could go negative (if requirements are more then the stock + deliveries) but in practice you cannot have negative pallets so I added MAX(pallet calculation,0) to remove negative numbers.

    I tried to change a bit your SUMMARIZE formula (where you define the new VAR) adding CALCULATE and FILTER with MAX(date) in the DAX expression but it is not working.

    Here I added a sample of data: https://www.dropbox.com/s/41iq6f968bsirpc/Book1.xlsx?dl=0

    sheet 1: made up data for 2 IDs

    sheet 2: what I would like to achieve for each ID (example of 1 ID)

    Also another problem I am facing is (see sheet 2 lines highlighted in red) when I have a gap in the data and some dates are missing (here we jump from 16/08 to 21/08). I am afraid that at the date 17/08,18/08,... the data will now show up because there is no datapoint for date for that specific ID in the table Forecast. How can I avoid this?

    Hope it makes sense!

    Thank you very much!





    Monday, July 15, 2019 6:09 PM
  • Also another problem I am facing is (see sheet 2 lines highlighted in red) when I have a gap in the data and some dates are missing (here we jump from 16/08 to 21/08). I am afraid that at the date 17/08,18/08,... the data will now show up because there is no datapoint for date for that specific ID in the table Forecast. How can I avoid this?

    You can solve this by adding a "Date" table to your model. This date table should have a continuous series of dates covering all the dates in your forecast table.

    Then your stock projection measure would be something like the following:

    Pallet Forecast = 
    MAX(
        CALCULATE([Pallet Delivery] , filter(all('Date'[Date]),'Date'[Date] <= max('Date'[Date]))) 
        , 0
    )


    http://darren.gosbell.com - please mark correct answers

    • Marked as answer by TommyWhammy Tuesday, July 23, 2019 8:48 PM
    Monday, July 15, 2019 11:54 PM
    Moderator