Running total that adds leftover from previous values

[jellyjoe]

Hello,

I've recently ran into an issue when creating a report.

I'm trying to create a table that looks like this (minus the incorrect by hour column):

It should calculate how many products should be produced for each timerange taking into account production plans for different lines. The thing is it's impossible to create eg. 0,571 of a product so we need to move the leftover from previous timerange to the next one and check if it gives us another product to be produced in the timerange.

I've managed to create a solution using calculated columns but it requires me to create columns for each line and doesn't seem scaleable. What would the optimal solution for this issue be?

I'm attaching the PBIX file with a more detailed explanation, dummy dataset and current unoptimal solution in the link below.

I'd really appreciate some support on this issue.

Link: https://drive.google.com/file/d/1YfGEEc_TvareO0rNfN2vtJVPh6J84Qrl/view?usp=drivesdk

Kind regards,

Damian

[MFelix]

Hi @jellyjoe ,

The file is asking a password can you share it again?

[jellyjoe · Accepted solution]

Hello @MFelix ,

Sorry for late answer.

I've found a solution to the issue using variables in measure

VAR _plan =

MAXX ( '[ePCB] Plan', '[ePCB] Plan'[Plan Prod])

VAR _op_time =

SUMX (

ALLSELECTED ( '[ePCB] Time range' ),

'[ePCB] Time range'[Operation time]

)

VAR _h_time =

DIVIDE ( _plan * MAX ( '[ePCB] Time range'[Operation time] ), _op_time )

VAR _1 = _h_time

VAR _2 =

_h_time

+ ( _1 - ROUNDDOWN ( _1, 0 ) )

VAR _4 =

_h_time

+ ( _2 - ROUNDDOWN ( _2, 0 ) )

VAR _5 =

_h_time

+ ( _4 - ROUNDDOWN ( _4, 0 ) )

VAR _7 =

_h_time

+ ( _5 - ROUNDDOWN ( _5, 0 ) )

VAR _8 =

_h_time

+ ( _7 - ROUNDDOWN ( _7, 0 ) )

VAR _10 =

_h_time

+ ( _8 - ROUNDDOWN ( _8, 0 ) )

VAR _11 =

_h_time

+ ( _10 - ROUNDDOWN ( _10, 0 ) )

RETURN

[MFelix]

Hi @jellyjoe ,

Glad you were abble to sort it out, don't forget to mark your answer so it can help others.